XLOOKUP is the newer lookup, available in Microsoft 365 and Excel 2021 and later:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Instead of a table and a column number, you give two ranges: the column to search and the column to return. That makes it easier to read and lets it look in any direction, including to the left.
- It does an exact match by default, so there's no FALSE to forget.
- The optional
if_not_found argument replaces #N/A with your own text, such as "Not found".
match_mode -1 returns the exact match or the next smaller value, which is how you look up a rate from bands like 0, 50, 100.
Make the lookup and return ranges the same height.