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
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"))