Forum Discussion
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?
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)), "")
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)), "")
Yes, it worked! I've tried so many formulas and it just didn't work...
Thank you so much!
