Forum Discussion
Help Vlookup formula with criteria
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))
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? |
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
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
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))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
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))- Thanks, it works!
Appreciated 😍
