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)?
Table | |
Country | Unit Price |
NZ1 | 100 |
NZ2 | 200 |
CN | 100 |
CN2 | 200 |
HK | 200 |
What formula should apply to able get the answer as expected?
Expect result from the table | ||
Origin country | Amount | Unit Price |
NZ | 100 | 100 |
NZ | 199.99 | 200 |
CN | 99.98 | 100 |
CN | 200.01 | 200 |
HK | 100 | 200 |
Jul 30 2020 01:42 AM
What is the logic behind the rounding of price range based on amount? For example, for NZ 199.99 goes to 200, what shall be for 187.31?
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) |
France | 3.68 |
New Zealand | 3.53 |
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? |
Jul 30 2020 04:38 AM
That could be as
formula
=INDEX($C$4:$C$6,
MATCH(1,INDEX(
ISNUMBER(SEARCH($E4,$B$4:$B$6))*
(ABS($C$4:$C$6-$G4)=MIN(ABS($C$4:$C$6-$G4))),0),0)
)
works as it on Excel with dynamic arrays, otherwise Ctrl+Shift+Enter
Aug 02 2020 10:38 PM
Aug 02 2020 11:14 PM
Solution
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):
=INDEX($C$4:$C$9, MATCH(1,ISNUMBER(SEARCH($E4,$B$4:$B$9))*(ABS($C$4:$C$9-$G4)=MIN(IF(ISNUMBER(SEARCH($E4,$B$4:$B$9)),ABS($C$4:$C$9-$G4),""))),0))
Aug 02 2020 11:30 PM
Another way is...
=INDEX($C$4:$C$9,MATCH(MIN(IF(ISNUMBER(SEARCH(E4,$B$4:$B$9)),ABS($C$4:$C$9-G4))),INDEX((ISNUMBER(SEARCH(E4,$B$4:$B$9)))*(ABS($C$4:$C$9-G4)),),0))
Aug 03 2020 12:10 AM
I believe you will have a problem if the charge/unit happens to match the price per min. Since the difference is zero, it will match to the first zero.@Subodh_Tiwari_sktneer
Aug 03 2020 02:14 AM
Aug 03 2020 07:34 AM
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 03 2020 11:59 AM
Ah... you are right. Thanks for pointing that out.
Aug 03 2020 03:23 PM
As variant
with
=IF(LEN($E4)=0,"",
XLOOKUP(
$E4&"*",
SORTBY($B$4:$B$9,ABS($C$4:$C$9-$G4)),
SORTBY($C$4:$C$9,ABS($C$4:$C$9-$G4)),
"no such",2)
)
if you are on Excel with dynamic arrays
Aug 04 2020 09:35 PM
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 )
Aug 02 2020 11:14 PM
Solution
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):
=INDEX($C$4:$C$9, MATCH(1,ISNUMBER(SEARCH($E4,$B$4:$B$9))*(ABS($C$4:$C$9-$G4)=MIN(IF(ISNUMBER(SEARCH($E4,$B$4:$B$9)),ABS($C$4:$C$9-$G4),""))),0))