Forum Discussion

paulj1's avatar
paulj1
Helper II
8 years ago

LOOKUPVALUE Nearest not exact

So i have a Table with column with  0, 2.5, 3.0, 3.5, 4.0, 4.5   I want to do a lookup value for nearest, in Excel id use VLOOKUP with the last parament True or False   So Looking up;   1.12 wo...
Need Help
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
8 years ago

Hi paulj1,

 

If I understand your scenario correctly that you have two tables and you want to look up the value to match the value in another table?

 

If it is,  you could have a reference of this simialr thread.

 

If you still need help, please share your data sample, so that we can help further investigate on it?

 

Best Regards,

Cherry

Anonymous's avatar
Anonymous
Not applicable
7 years ago

The thread you referenced is not the same issue. 

 

What if the "Search_Value" that we use in the LOOKUPVALUE column does not exist in the "Search_columnName". I'd like it to return the closest value in the "Search_columnName". 

 

In the original question 1.12 does not exist in our lookuptable but we want it to return 0 since that is the closest. Here is another thread which mentions a potential solution: https://stackoverflow.com/questions/52525377/return-top-value-ordered-by-another-column

 

You can modify the formula to find the closest value. Here is the result I came up with:

Closest Match Measure =
MAXX (
    TOPN (
        1,
        LookupTableName,
        ABS (
            CALCULATE (
                SUM ( 'DataTable'[SearchValue] ) - SUM ( LookupTableName[ReturnValue] )
            )
        ), ASC
    ),
    LookupTableName[LookupValue]
)

Basically we are just finding the smallest difference between our value and the values in the lookup table, and returning the result. 

Note that the above formula may need to me modified depending on whether you have relationships between your tables. I have found that storing the measure as a variable works in these situations

Closest Match using VAR =
VAR myMeasure = [MeasureName]
RETURN
    MAXX (
        TOPN (
            1,
            LookupTableName,
            ABS ( CALCULATE ( myMeasure - SUM ( LookupTableName[ReturnValue] ) ) ), ASC
        ),
        LookupTableName[LookupValue]
    )

 

 

 

  • rgstevens's avatar
    rgstevens
    Frequent Visitor
    7 years ago

    Anonymous 's solution works well for when there is only one lookup table. Do you have any suggestions for how this could work if there is a larger table involved, where there are multiple filter criteria?

     

    Example below works for finding exact values in a large table that has 5 different domains and 4 different years. But it needs to be able to find the closest match and not just any exact matches.

     

    Band = LOOKUPVALUE('Table 1'[Band],'Table 1'[Year],"9",'Table 1'[Domain],"Reading",'Table 1'[Scale Score],'2016 YR9 OutcomeLevelData'[READING_nb])
     
    Edited - With limited knowledge this is what I came up with
     
    Reading Band = CALCULATE(
MAXX (
    TOPN (
        1,
        'Table 1',
        ABS (
            CALCULATE (
                SUM ( 'Table 1'[Scale Score] ) - SUM ( '2016 YR9 OutcomeLevelData'[READING_nb] )
            )
        ), ASC
    ),
    'Table 1'[Band]
),
FILTER('Table 1','Table 1'[Domain]="Reading"),
FILTER('Table 1','Table 1'[Year]="9"))
  • HeyDax's avatar
    HeyDax
    New Member
    4 years ago

    Hey! I know this is an old post, but hoping someone can help. Is there a way to modify Anonymous 's code so that instead of finding the smallest absolute value it finds the closest value in the lookup table that is not greater than the search value? (e.g. if search value is 9 and the closest lookup values are 5 and 10, I want it to return 5 since it's the closest match that is not greater than the seartch value (even though 10 has a smaller absolute difference)