Answered by:
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
Answers

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] ) ) )
 Proposed as answer by Taylor N ClarkMicrosoft employee Monday, June 2, 2014 10:27 PM
 Marked as answer by MaYaLi Wednesday, June 4, 2014 1:13 AM
 Edited by Taylor N ClarkMicrosoft employee Wednesday, June 4, 2014 4:56 PM
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] ) ) )
 Proposed as answer by Taylor N ClarkMicrosoft employee Monday, June 2, 2014 10:27 PM
 Marked as answer by MaYaLi Wednesday, June 4, 2014 1:13 AM
 Edited by Taylor N ClarkMicrosoft employee Wednesday, June 4, 2014 4:56 PM
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 mindbending 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 :)
 Edited by Taylor N ClarkMicrosoft employee Wednesday, June 4, 2014 4:57 PM
Wednesday, June 4, 2014 4:55 PM