Jul 30 2020 01:26 AM
Jul 30 2020 01:26 AM
If I have a table, what formula would I use to return a value to a cell-based upon two lookup criteria (match country & in price range)?
What formula should apply to able get the answer as expected?
|Expect result from the table|
|Origin country||Amount||Unit Price|
Jul 30 2020 02:35 AM - edited Jul 30 2020 02:36 AM
Fixed my sample:
this my contract pricing table:
|Country||Price per Min (INR)|
|New Zealand - Mobile||21.87|
This the report received, as the report didn't tell whether it for mobile or normal type.
The only way to identify is to match the country & match to the nearest rate to get my contract rate.
It may also happen the rate total not close.
Purpose to check whether it charges as per agreed contract rate
|Origin country||Duration||Charge Unit||Contract Rate (formula)|
|NEW ZEALAND||132||21.8658||to get contract rate 21.87?|
|NEW ZEALAND||30||3.53||to get contract rate 3.53?|
|FRANCE||522||12.25||to get contract rate 3.68?|
Aug 02 2020 11:14 PMSolution
If I may, I think the problem is that the MIN function is evaluating all of the rate differences and not just the rate differences for the selected country.
I think it just needs a little tweak. Try this in H4 (copied down):
Aug 03 2020 08:29 AM - edited Aug 03 2020 08:36 AM
I get a different answer by changing the charge/unit to 21.87. Is the charge/unit in your screencap 21.87 or just formatted to show 2 decimals?
If the charge/unit is the same as the price per min for the applicable country, then the min value for that country is zero. Then MATCH will match to the first zero in the second argument, which may/may not be correct as the values for the other countries will be zero as well.
Aug 05 2020 01:35 AM
For my own benefit, I first implemented @Sergei Baklan 's formula using defined names
= IF(LEN(@Origin_country)=0,"", XLOOKUP( @Origin_country&"*", SORTBY(Country, ABS(Price-@Charge)), SORTBY(Price, ABS(Price-@Charge)), "no such",2) )
(I have come to believe I am the only person on the planet that finds names more readable)
Instead of using XLOOKUP to perform a wildcard search of a sorted array, I decided to use XLOOKUP to search for the filtered value closest to zero.
= IF( LEN(@Origin_country), XLOOKUP( 0, priceVariance, tblPrice, "no such", 1 ), "" )
where the name 'priceVariance' refer to the formula
= IF( (LEFT(Country, LEN(@Origin_country)) = @Origin_country), ABS(Price - @Charge), -1 )
Using the newly released LET function, this could be combined
= LET( countryFound?, LEFT(Country, LEN(@Origin_country)) = @Origin_country, priceVariance, IF( countryFound?, ABS(Price - @Charge), -1 ), closestPrice, IF(LEN(@Origin_country), XLOOKUP( 0, priceVariance, tblPrice, "no such", 1 ), "" ), closestPrice )