Forum Discussion

Sara19845's avatar
Sara19845
Copper Contributor
Sep 22, 2022

Vlookup closest higher match

 

Tab A: "Column A" has a list of weight amounts. Then in "Column B" I want the shipping price for that. So in Tab B, "Column A" has weight amounts, and "Column B" has the shipping prices for the weight in column A. So I want a formula to enter into tab A column B to return the shipping price of the closest higher match in tab B.

Can anyone please help me out here?

  • Sara19845 

    If you have Microsoft 365 or Office 2021, on A2 on Tab A:

     

    =XLOOKUP(A1, 'Tab B'!$A$2:$A$1000, 'Tab B'!$B$2:$B$1000, "", 1)

     

    Replace Tab B with the real name of that sheet, then fill down.

    Remark: column A on Tab B must be sorted in ascending order for this to work.

     

    If you have an older version, use the following formula and confirm it by pressing Ctrl+Shift+Enter:

     

    =IFERROR(INDEX('Tab B'!$B$2:$B$1000, MATCH(TRUE, 'Tab B'!$A$2:$A$1000>=A2, 0)), "")

  • Sara19845 

    If you have Microsoft 365 or Office 2021, on A2 on Tab A:

     

    =XLOOKUP(A1, 'Tab B'!$A$2:$A$1000, 'Tab B'!$B$2:$B$1000, "", 1)

     

    Replace Tab B with the real name of that sheet, then fill down.

    Remark: column A on Tab B must be sorted in ascending order for this to work.

     

    If you have an older version, use the following formula and confirm it by pressing Ctrl+Shift+Enter:

     

    =IFERROR(INDEX('Tab B'!$B$2:$B$1000, MATCH(TRUE, 'Tab B'!$A$2:$A$1000>=A2, 0)), "")

    • Sara19845's avatar
      Sara19845
      Copper Contributor

      HansVogelaar 

      Yes, it worked! I've tried so many formulas and it just didn't work...

       

      Thank you so much!

Share

Resources