Forum Discussion

eehyun123's avatar
eehyun123
Copper Contributor
Jul 30, 2020
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 ...
excel
  • JMB17's avatar
    JMB17
    Aug 03, 2020

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

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 05, 2020

eehyun123 

For my own benefit, I first implemented SergeiBaklan '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 )

 

 

Resources