Function analog vlookup excel

antik1315
antik1315
in Modeling

Hello and thank you all for your time and help. I have 2 lists: "Test List 1" and "Test List 2", where "Test list 2" is created based on "Test list 1" according to the following rules:

For each item has a text property

Formula RANK =  RANK(1, ASCENDING, SEQUENTIAL, TRUE, Text property)

Next, I filtered those items that have a rank =1, and created "Test List 2"

 

List 1.JPG

Below is a created "Test List 2"

List 2.JPG

I want for each items "Test List 1" to find item "Test List 2" according property "Text Property". The required result is indicated below in green.

Result.JPG

I know that this problem can be solved by using as a code "Text Property" for "Test List 2". Next use function finditem(Test List 2, Text property), but in real a lenght of code is more than 60 characters, and I will have a problem, when I will create List.

 

Thanks in advance!!

Tagged:

Answers

  • rob_marshall
    rob_marshall

    @antik1315 

     

    Is there any way your text could be a list formatted line item?  I ask because of two reasons:

    • text is bad for performance and should be avoided
    • You should not have properties in a list, have them as line items in a module
    • Having it as a list formatted line item will allow you do exactly what you are wanting in a very simple manner.

    Let's assume, for my sake, that the text column is list formatted.

     

    You will need two modules:

    • Trans module which uses List 1 and has these "properties" as line items
    • Newly created line item module  (formerly the Text, I will call this Text Flat)

     

    Text Flat module is dimensionalized by Text Flat with one line item formatted as List 2 with the formula:

    Transmodule.'List 2'[FirstNonBlank: Transmodule.FormerTextLineItem]

    • This will get the first occurrence and now you can do a lookup to pull that value in for the Trans Module.

    Rob

Categories