VLOOKUP returns #N/A here because P-999 is not present in the first column of D2:E7. The live sheet lets you replace it with an existing ID and watch the error disappear.
Search for a value in the first column of a table and retrieve data from another column in the same row.
A2The value to search for in the first column of the range.D2:D7VLOOKUP always searches the first column of the table.E2:E7The column number in the table from which to retrieve the value.FALSEExactIf TRUE or omitted, it finds an approximate match. If FALSE, it looks for an exact match.VLOOKUP searches D2:D7 for the value in A2. Exact-match mode refuses to settle for a neighboring ID, so the missing P-999 produces #N/A.
The rows above are ordered from the innermost calculation to the final result. Excel has to finish an inner function before the function around it can use that value. Edit a cell in the grid to see that dependency chain update for real.
The sample spreadsheet is fully editable. Change the formula or any cell it references. The intermediate values recalculate in your browser.
Here is an exercise that uses VLOOKUP. Type the formula into a real spreadsheet and we will check your answer as you go.
Look up an employee's department using their employee ID.
Open exerciseIf this formula is not doing what you expect, one of these is usually why.
The lookup value is absent from the first column of the table, or Excel considers it different. Trailing spaces and numbers stored as text are common causes because both can look correct on screen.
The last argument is TRUE or missing, so VLOOKUP settles for an approximate match. Use FALSE unless the first column is sorted and you deliberately want a banded lookup.
The column number is larger than the table is wide. D2:E7 is two columns wide, so 3 has nowhere to go. Count from the left edge of the selected table.
No. VLOOKUP always searches the first column of the table and returns something to its right. Use INDEX and MATCH or XLOOKUP when the answer is to the left.