Related/Vlookup

• Question

• Hi,

I need to use a function to retrieve data from another table.

I know how to use related if the data exists in another table, which is like Excel's vlookup with the parameter 0 (Exact),

But I need to get an approximate value (Excel's parameter 1)

How can I do that?

(I would like to attach an example file, but I don't see how...)

Thanks

Monday, June 2, 2014 12:30 PM

• Greetings,

Give this a try in your calculated column. Here we're taking advantage of the fact that Prcnt[Percent] and Prcnt[Sale] increase together (if one is bigger, they both are)

=CALCULATE(Max(Prcnt[Percent]),FILTER(ALL(Prcnt),Prcnt[Sale]<= Earlier(Sales[sale])))

Here's a quick example of a general case:

AllNumbers table

Number    SmallestEven
1
2    2
3    2
4    4
5    4

EvenNumbers table

Number
2
4
6

Where the formula for SmallestEven is

=CALCULATE( Max( EvenNumbers[Number] ), Filter(All(EvenNumbers), EvenNumbers[Number]<= Earlier( MainTable[Number] ) ) )

Monday, June 2, 2014 10:25 PM

All replies

• Greetings,

Give this a try in your calculated column. Here we're taking advantage of the fact that Prcnt[Percent] and Prcnt[Sale] increase together (if one is bigger, they both are)

=CALCULATE(Max(Prcnt[Percent]),FILTER(ALL(Prcnt),Prcnt[Sale]<= Earlier(Sales[sale])))

Here's a quick example of a general case:

AllNumbers table

Number    SmallestEven
1
2    2
3    2
4    4
5    4

EvenNumbers table

Number
2
4
6

Where the formula for SmallestEven is

=CALCULATE( Max( EvenNumbers[Number] ), Filter(All(EvenNumbers), EvenNumbers[Number]<= Earlier( MainTable[Number] ) ) )

Monday, June 2, 2014 10:25 PM
• Thanks

I guess I'm doing something wrong...

I attached the file -

the answer for Dan should be 0.1

And for Moses 0.15

﻿https://www.dropbox.com/s/51rlellx2lbkc5z/RelatedVlookup.xlsx

Tuesday, June 3, 2014 12:28 AM
• Great!

Now I just have to understand the formula (:

Wednesday, June 4, 2014 1:13 AM
• Here's a look at how the formula works, broken down into it's pieces. I'll use the numbers example for anyone who stumbles across this in the future.

Filter( All(  EvenNumbers ), EvenNumbers[Number] <= Earlier( AllNumbers[Number] ) )

The Filter function takes two arguments, a table and an expression. Filter takes the table you give it, and "filters" it down to only the rows for which the expression is true.
All(EvenNumbers) give me all rows of the EvenNumbers table.

If we write Filter( All( EvenNumbers ), TRUE() ), we get all rows of the EvenNumbers table. If we write Filter( All(EvenNumbers), EvenNumbers[Number] <= 2 ), we'd EvenNumbers, but only with one row (where [Number]=2).

Earlier( AllNumbers[Number] ) is a bit complicated, but can be thought of as "the value of AllNumbers[Number] for the current row in the calc column". In the first row of AllNumbers it returns 1, in the second 2, etc.

If we combine the above, we get "Look at all rows of the EvenNumbers table, and give me the rows where the EvenNumbers[Number] column is <= AllNumbers[Number]". If you write another calculated column in AllNumbers with the formula CountRows( Filter(All(EvenNumber), EvenNumber[Number] <= Earlier(AllNumbers[Number]) ) ) (and gave it a very long column name),  you'd get this:

AllNumbers table

Number    Count of rows in EvenNumber which have [Number] <= AllNumbers[Number] in this row
1       0
2    1
3    1
4    2
5    2

Instead of counting rows, we want to take the filtered EvenNumbers table, and get the maximum value of [Number] in the remaining rows. We can use the Calculate function, giving it our filtered table to look at.

=Calculate( Max( EvenNumbers[Number] ), Filter(All(EvenNumbers), EvenNumbers[Number]<= Earlier( AllNumbers[Number] ) ) )

Hope this helps! Let me know if something's particularly confusing (sometimes these formulas work in mind-bending ways). I you're interested, I can provide a more complete example, with better step by step breakdowns, but alas, I think this post is long enough :)

Wednesday, June 4, 2014 4:55 PM