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

     

Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Aug 02, 2020

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))
JMB17's avatar
JMB17
Bronze Contributor
Aug 03, 2020

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 

    • JMB17's avatar
      JMB17
      Bronze Contributor
      Aug 03, 2020

      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.

Resources