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