Lookup formulas
Find a value and bring back what goes with it: by ID, by two keys, the last match, the closest match. 6 answers, each checked by running it, with a live example and an exercise.
How to use VLOOKUP to find an exact match in Excel
=VLOOKUP(G1,A2:D9,3,FALSE)Use =VLOOKUP(lookup_value, table, column_number, FALSE). The FALSE at the end makes it look for an exact match.
How to look up a value to the left in Excel
=XLOOKUP(G1,D2:D7,A2:A7)Use XLOOKUP, which can return any column: =XLOOKUP(value, lookup_column, result_column). In older versions use INDEX and MATCH.
How to look up a value with two criteria in Excel
=XLOOKUP(1,(A2:A8=G1)*(B2:B8=G2),D2:D8)Multiply two tests to build the lookup array: =XLOOKUP(1,(range1=crit1)*(range2=crit2),result_range).
How to find the last occurrence of a value in Excel
=XLOOKUP(F1,B2:B8,A2:A8,,0,-1)Use XLOOKUP with search mode -1 to search from the bottom up: =XLOOKUP(value, lookup_range, return_range,,0,-1).
How to look up a value between two numbers in Excel
=XLOOKUP(E1,A2:A6,B2:B6,,-1)Put the lower bound of each band in a sorted column and use an approximate match: =XLOOKUP(value, lower_bounds, results,,-1).
How to return a message instead of #N/A in a lookup
=XLOOKUP(G1,A2:A9,C2:C9,"Not found")Give XLOOKUP its if_not_found argument, =XLOOKUP(value, lookup, return, "Not found"), or wrap an older lookup in IFNA.