Forum Discussion
Solved
Help Vlookup formula with criteria
Help Vlookup formula with criteria 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 ...
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