Forum Discussion
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)), "")
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!
