Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Vlookup closest higher match

Copper Contributor

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.

2 Replies
best response confirmed by Sara19845 (Copper Contributor)
Solution

Re: Vlookup closest higher match

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

Re: Vlookup closest higher match

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

Thank you so much!

1 best response

Accepted Solutions
best response confirmed by Sara19845 (Copper Contributor)
Solution

Re: Vlookup closest higher match

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