LOOKUPVALUE Nearest not exact

[paulj1]

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 would return 0

2.62 would return 2.5

4.01 would return 4.0

Etc

How do i do this.....

Thanks in advance

Paul

[TheMike]

EDIT: solved - it's actually working - I had typed an incorrect year and was looking at a wrong date.

I'm trying to get this working for my case.

I have two tables:

1. Table 1 - Products: product name, dates, price on date, etc.
2. Table 2 - Exchange rates: unique dates, currencies, exchange rate

I'm trying to create a measure that, for each row of the Table 1 Products:

• Gets the date
• Finds the currency of that product (done)
• Finds the nearest date in Table 2 Exchange Rates
• Gets the exchange rate for that currency on the selected (closest) date

I've done a few attempts but so far what I get is:

- A date that is not the closest one

- No exchange rate for the [incorrectly] found closest date (even if it exists)

The measure is only working if there is an exact same date on Table 2 Exchange rates (which is impractical).

Below are my tables (sample):

Table 1 Products

Date	Product	Price	Currency	Amount	TOtal
26/02/2022	XPTO	€    23.86	EUR	590	14074.45
31/01/2022	XPTO	€    24.08	EUR	590	14207.20
31/12/2021	XPTO	€    19.31	EUR	590	11392.90
21/12/2021	XPTO	€    19.44	EUR	590	11468.42
17/12/2021	XPTO	€    18.80	EUR	590	11089.64
10/12/2021	XPTO	€    19.44	EUR	590	11467.24
04/12/2021	XPTO	€    19.36	EUR	590	11421.22
24/11/2021	XPTO	€    18.36	EUR	590	10833.58

Table 2 Exchange Rates

Date	USD	EUR	GBP
27/02/2022	5.16	5.82	6.92
31/01/2022	5.33	6.10	7.21
15/01/2022	5.53	6.32	7.57
31/12/2021	5.57	6.33	7.54
21/12/2021	5.75	6.48	7.62
17/12/2021	5.70	6.40	7.54
10/12/2021	5.61	6.35	7.45
05/12/2022	5.65	6.40	7.48
24/11/2021	5.61	6.35	7.48
19/11/2021	5.61	6.34	7.55

My latest attempt in the measure:

=maxx(
   TOPN (
        1,
        'Table2_Exchange_Rates',
        ABS(
           CALCULATE(
                     sum('Table1_Products'[Date]) - 
                     sum('Table2_Exchange_Rates'[Date]) 
                    )
         ),ASC
    ),
   'Table2_Exchange_Rates'[Date]
)

Thanks

[cjayaneththi]

1.12 would return 0 ???

is this correct? or it should be 1

[paulj1]

Yes it is correct,needs to return a value that exists in the reference table....

[v-piga-msft]

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]

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

[HeyDax]

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)

[rgstevens]

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