How to use VLOOKUP in Excel (and when to use XLOOKUP)

01The four arguments of VLOOKUP

The formula looks like this: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

• lookup_value: what you are searching for, for example the ID in A2.
• table_array: the range to search. The value you look up must be in the first column of this range.
• col_index_num: which column of the range to return, counting from 1.
• FALSE: exact match. Always use FALSE for IDs and codes, or you may get a wrong row.

02Example: find a name from a student ID

1. Say the sheet "Roster" has IDs in column A and names in column B.
2. In C2 of your grades sheet, type: =VLOOKUP(A2, Roster!$A:$B, 2, FALSE)
3. Press Enter, then drag the small square at the bottom right of C2 down to fill the column.

03What to do about #N/A

• The values look the same but one is a number and the other is text. Format both columns the same way and try again.
• There are spaces before or after the value. Clean it with =TRIM(A2).
• The value really is missing. Wrap the formula: =IFERROR(VLOOKUP(…), "Not found").
• The value you search for is not in the first column of the range. VLOOKUP can only look to the right, so use XLOOKUP instead.

04Newer Excel: use XLOOKUP

Microsoft 365 and Excel 2021 or later have XLOOKUP. It does not need a column number and can look to the left.

1. Formula: =XLOOKUP(A2, Roster!A:A, Roster!B:B, "Not found")
2. The first argument is the value to find, the second is the column to search, the third is the column to return, and the fourth is the text to show when nothing matches.

Q&AFAQ