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

     

14 Replies

Sort By
  • 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 )

     

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 30, 2020

    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?

    • eehyun123's avatar
      eehyun123
      Copper Contributor
      Jul 30, 2020

      SergeiBaklan 

       

      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?
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 30, 2020

        eehyun123 

        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

Resources