Vlookup closest higher match

[Sara19845]

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?

[#2 · Accepted solution]

@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)), "")

[#3]

@Hans Vogelaar 

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

Thank you so much!