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
