SOLVED

Help Vlookup formula with criteria

Copper Contributor

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
CountryUnit Price
NZ1100
NZ2200
CN100
CN2200
HK200

 

 What formula should apply to able get the answer as expected?

  Expect result from the table
Origin countryAmountUnit Price
NZ100100
NZ199.99200
CN99.98100
CN200.01200
HK100200
14 Replies

@eehyun123 

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?

@Sergei Baklan 

 

Fixed my sample:

 

this my contract pricing table:

CountryPrice per Min (INR)
France3.68
New Zealand3.53
New Zealand - Mobile21.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 countryDurationCharge UnitContract Rate (formula) 
NEW ZEALAND         13221.8658 to get contract rate  21.87?
NEW ZEALAND         303.53 to get contract rate   3.53?
FRANCE              52212.25 to get contract rate   3.68?

@eehyun123 

That could be as 

image.png

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

@Sergei Baklan 

 

Thank for helping.

However, it not work when I add additional country.

Any suggestion?

 

best response confirmed by eehyun123 (Copper Contributor)
Solution

@eehyun123 

 

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

 

@eehyun123 

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 

Thanks, it works!
Appreciated :smiling_face_with_heart_eyes:

@JMB17 

Not sure why you got a different output.

 

Lookup prices.jpg

@Subodh_Tiwari_sktneer 

 

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.

@JMB17 

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

@eehyun123 

As variant

image.png

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

Thanks @Sergei Baklan 

 

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

@eehyun123 

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 )

 

 

1 best response

Accepted Solutions
best response confirmed by eehyun123 (Copper Contributor)
Solution

@eehyun123 

 

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

 

View solution in original post