PlayInfoBox

Home /Utilities /How to Use the VLOOKUP Function in Excel

How to Use the VLOOKUP Function in Excel

Tap through each step to look up values automatically in Excel with VLOOKUP.

  1. Click the cell where the result should appear

    Click an empty cell where you want the looked-up value to show up.

  2. Enter the VLOOKUP formula

    Type a formula in the form "=VLOOKUP(lookup_value, table_range, column_number, FALSE)."

  3. Set the table range and column number

    Specify the full table range starting from the column that contains the value you're looking up, then enter the column number, counted from the left, of the column you want returned.

  4. Use FALSE (or 0) for an exact match

    Entering FALSE as the last argument finds only an exact match, and shows a "#N/A" error if nothing matches.

  5. Why this is useful

    Whether you're looking up a company name from a customer code or a name from a student ID, VLOOKUP pulls the matching value out of a large table instantly, without any repetitive manual searching.

The most common #N/A cause

Hidden leading or trailing spaces in the lookup text, a mismatch between text and number formatting on either side, or the lookup column simply not being the first column of the selected table range are the three most frequent reasons VLOOKUP fails even when the value clearly exists somewhere in the sheet.

VLOOKUP's one-directional limit β€” and what replaces it

VLOOKUP can only search to the right of the first column in its table range. If you need to look leftward, or want more flexible matching, pairing INDEX with MATCH removes that restriction, and newer versions of Excel offer XLOOKUP as an even more direct replacement.

Frequently Asked Questions

Why does VLOOKUP return #N/A even though the value clearly exists in the table?

The usual causes are invisible extra spaces around the text, a data-type mismatch where one side is text and the other a number, or the lookup column not being the leftmost column in the table range you selected.

What's the difference between VLOOKUP and XLOOKUP?

XLOOKUP is the newer function: it can search either left or right, doesn't require counting a column number, and handles missing matches more gracefully. It's only available in newer Excel versions and Microsoft 365, which is why VLOOKUP remains widely used for compatibility with older files.