 SOLVED

Highlighted

# 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 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
14 Replies
Highlighted

# Re: Help Vlookup formula with criteria

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?

Highlighted

# Re: Help Vlookup formula with criteria

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

# Re: Help Vlookup formula with criteria

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

Highlighted

# Re: Help Vlookup formula with criteria

Thank for helping.

Any suggestion?

Highlighted
Best Response confirmed by eehyun123 (Occasional Contributor)
Solution

# Re: 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))``

Highlighted

# Re: Help Vlookup formula with criteria

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))``
Highlighted

# Re: Help Vlookup formula with criteria

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

Highlighted

# Re: Help Vlookup formula with criteria

Thanks, it works!
Appreciated
Highlighted

# Re: Help Vlookup formula with criteria

Not sure why you got a different output. Highlighted

# Re: Help Vlookup formula with criteria

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.

Highlighted

# Re: Help Vlookup formula with criteria

Ah... you are right. Thanks for pointing that out.

Highlighted

# Re: Help Vlookup formula with criteria

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

Highlighted

# Re: Help Vlookup formula with criteria

Thanks @Sergei Baklan

this work with using latest formula _ xlookup . Help me learn alot !

Highlighted

# Re: Help Vlookup formula with criteria

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