LOOKUPVALUE with closest value

[mrshl9898]

Hi all,

I'm needing to lookup a value from another table. The below could match 6 rows though.

=LOOKUPVALUE('RATE CARD'[value],'RATE CARD'[Origin],DELIVERIES[PickupResponsibleBranchCode],'RATE CARD'[ToLocation],DELIVERIES[ToRatingLocation])

The key here would be 'RATE CARD'[BreakPoint]

breakpoint	value
1000	0.2583
12000	0.237
250	0.3915
500	0.2912
3000	0.2457
999999	0.237

These figures are the maximum in a range of weights, 250 is 0-250, 500 is 251-500 etc...

If I want to return a match for 375 (0.2912), what do I need to add to the Formula? Keeping in mind these numbers will change from time to time, so I need the closest greater than result.

The kilos are found in DELIVERIES[ChargeWeight].



Many thanks for any help here.

 

[Saba Sabaratnam]

Hi

My solution is based on the following spreadsheet layout of data and you can modify it to suit your situation.

Organise breakpoints in ascending order as shown above

Enter the following formula in C13


=INDEX($C$3:$C$8,MATCH(B13,$B$3:$B$8,1)+1)

Kind regards

Saba

 

[mrshl9898]

Thanks Saba, but wrong forum, need a DAX solution.

 

[Saba Sabaratnam]

Sorry I misunderstood it

 

[JustynaMK]

Maybe this measure will help
It should work in the same manner as Excel's approximate match.

mLookup =
    var vSearch = 375
    var vLower = CALCULATE(MAX('RATE CARD'[breakpoint]), FILTER('RATE CARD', 'RATE CARD'[breakpoint] <= vSearch))
    var vHigher = CALCULATE(MIN('RATE CARD'[breakpoint]), FILTER('RATE CARD', 'RATE CARD'[breakpoint] >= vSearch))
return
    IF(ABS(vSearch - vLower) <= ABS(vSearch - vHigher),
        LOOKUPVALUE('RATE CARD'[value], 'RATE CARD'[breakpoint], vLower),
        LOOKUPVALUE('RATE CARD'[value], 'RATE CARD'[breakpoint], vHigher))

 

[mrshl9898]

Bloody legend, much appreciated!

 

[JustynaMK]

☺ thank you! Take care