US Trends

how to use vlookup in excel

VLOOKUP in Excel lets you search for a value in the first column of a table and return related data from another column in the same row. It is ideal for looking up codes, names, prices, IDs, and similar reference data.

Quick Scoop

  • VLOOKUP syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
  • It always searches the leftmost column of the range you select and then moves to the right to pick the result.
  • You choose whether you want an exact match (most common) or an approximate match.

1. The core idea (simple story)

Imagine you have a big table of employee data: column A has employee names, column B has departments, and column C has salaries. On another sheet, you type a name and want Excel to “go to that big table, find this name in column A, and bring back the salary from column C.”

That is exactly what VLOOKUP does: it looks vertically down the first column of your table, finds the row with your lookup value, then jumps across to the column you specify and returns that value.

2. VLOOKUP syntax, explained

Basic form: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Here’s what each part means:

  • lookup_value
    The value you want to find in the first column of your table (for example, a name, ID, or code).
* Often this is a cell reference, like `A2`, not a typed value.
  • table_array
    The range of cells that contains both the column to search and the columns you want to return from.
* Example: `A2:D100`.
* The lookup_value must be in the **leftmost column** of this range.
  • col_index_num
    The column number, within your table_array, from which to return the result.
* The first column in the table_array is 1, the second is 2, and so on.

* Example: if `A2:D100` is your table and you want the value from column D, then `col_index_num` is 4.
  • [range_lookup] (optional but important)
    • FALSE (or 0) = exact match. This is what you should use most of the time for IDs, names, codes.
* `TRUE` (or 1) = **approximate match** , used mainly for ranges like grading or tax brackets, and the first column must be **sorted ascending**.

3. Step-by-step: basic VLOOKUP (exact match)

Example scenario:
You have a product list in Sheet1 (A2:D100):

  • Column A: Product ID
  • Column B: Product Name
  • Column C: Category
  • Column D: Price

On Sheet2, in cell A2, you enter a Product ID and want to return the corresponding price in B2.

Steps

  1. Click the result cell
    • Go to Sheet2 and click cell B2 (where you want the price).
  2. Start the formula
    • Type =VLOOKUP(.
  3. Enter lookup_value
    • Click cell A2 (the Product ID you typed).
    • Now you have: =VLOOKUP(A2,
  4. Enter table_array
    • Switch to Sheet1 and select the whole product table, for example A2:D100.
 * Excel will show something like: `=VLOOKUP(A2,Sheet1!A2:D100,`
  1. Enter col_index_num
    • Price is in column D of that table.
    • Within A2:D100, column A = 1, B = 2, C = 3, D = 4.
 * Type `4,` so you get: `=VLOOKUP(A2,Sheet1!A2:D100,4,`
  1. Choose exact or approximate match
    • For IDs, you want an exact match , so type FALSE.
    • Now formula is: =VLOOKUP(A2,Sheet1!A2:D100,4,FALSE)
  2. Close and confirm
    • Type ) and press Enter.
    • Excel will return the price corresponding to the Product ID in A2.

4. Example formula (concrete)

A complete example:

  • Lookup value in B6
  • Table range B2:D10
  • Want the value from the 3rd column of that range
  • Exact match

Formula:
=VLOOKUP(B6,B2:D10,3,FALSE) In this example, Excel looks for the value in B6 in column B of B2:D10, then returns the matching value from column D (the 3rd column of that range).

5. Using VLOOKUP across sheets or workbooks

You can use VLOOKUP even when the data is on another sheet or in another workbook.

Across sheets (same file)

  1. In the results sheet, click the cell where you want the result.
  2. Start typing =VLOOKUP(.
  3. Click the cell with the lookup value (on the current sheet).
  4. Switch to the sheet that has the table and select the range.
  1. Type the column index and FALSE or TRUE.
  2. Close the bracket and press Enter.

Excel automatically inserts the sheet name, for example:
=VLOOKUP(A2,Products!A2:D100,3,FALSE)

Across workbooks (different files)

  1. Open both workbooks and make sure they are visible.
  1. In the destination workbook, start =VLOOKUP(.
  2. Click the lookup value cell.
  3. Switch to the source workbook and select the table range.
  1. Type the column index and FALSE or TRUE, then close ).
  2. Press Enter.

The formula will include the file and sheet reference automatically.

6. Common VLOOKUP mistakes and fixes

VLOOKUP is powerful but a bit picky. Here are frequent issues and how to fix them.

1) #N/A errors

  • Reason:
    • The lookup value does not appear in the first column of the table_array.
    • There are extra spaces or hidden characters.
    • Data types differ (text vs number).
  • Fixes:
    • Make sure the lookup value is in the leftmost column of the selected range.
* Clean spaces using `TRIM()` on the data, or manually remove trailing spaces.

* Check that both the lookup cell and the table column are stored as the same type (both numbers or both text).

2) Wrong result (especially when using TRUE)

  • Reason:
    • Using TRUE (approximate match) with an unsorted first column can return unexpected results.
  • Fixes:
    • For IDs, names, codes: always use FALSE.
    • If you must use TRUE, sort the first column ascending.

3) #REF! or #VALUE! errors

  • Reason:
    • col_index_num is larger than the number of columns in your table_array.
* Using a wrong or non-numeric column index.
  • Fix:
    • Recount columns in your table_array and adjust the column index so it stays within that range.

4) Case sensitivity expectations

  • VLOOKUP is case-insensitive , so “ABC123” and “abc123” are treated as the same.

7. When NOT to use VLOOKUP (and what people say in forums)

On Excel forums, many experienced users say things like:

“First off, probably don't use VLOOKUP. There's almost always something better.”

The main reasons:

  • VLOOKUP can only look to the right of the lookup column.
  • It breaks if you insert or move columns, since the column index is fixed.

Alternatives such as INDEX + MATCH or newer functions like XLOOKUP are more flexible (look left or right, handle errors more gracefully), and tutorials often recommend them over VLOOKUP in modern Excel.

That said, VLOOKUP is still widely taught and used, and understanding it is very helpful when working with older spreadsheets or other people’s files.

8. Quick mini-checklist for using VLOOKUP

Before you hit Enter on your formula, check:

  1. Is your lookup value cell correct? (e.g., A2)
  2. Does your table_array start with the column that contains the lookup values?
  3. Is your col_index_num counted correctly from the left of the table_array?
  4. Did you set [range_lookup] to FALSE for exact matches?
  5. Are there any suspicious spaces or mixed data types in your lookup column?

9. HTML table – VLOOKUP cheat sheet

Below is a compact HTML table you can reuse in a post or notes:

html

<table>
  <thead>
    <tr>
      <th>Element</th>
      <th>What it means</th>
      <th>Example value</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>lookup_value</td>
      <td>Value to search for in the first column of the table.</td>
      <td>A2 (Product ID typed by user)</td>
    </tr>
    <tr>
      <td>table_array</td>
      <td>Range containing the lookup column and the return column(s).</td>
      <td>Sheet1!A2:D100</td>
    </tr>
    <tr>
      <td>col_index_num</td>
      <td>Column number (within table_array) to return the result from.</td>
      <td>4 (for the 4th column in A:D)</td>
    </tr>
    <tr>
      <td>range_lookup</td>
      <td>FALSE for exact match, TRUE for approximate match.</td>
      <td>FALSE (recommended for IDs/names)</td>
    </tr>
    <tr>
      <td>Full formula</td>
      <td>Looks up Product ID in column A and returns price from column D.</td>
      <td>=VLOOKUP(A2,Sheet1!A2:D100,4,FALSE)</td>
    </tr>
  </tbody>
</table>

10. SEO-style meta description

“How to use VLOOKUP in Excel” explained with a clear syntax breakdown, step- by-step examples, common error fixes, and tips from real forum discussions so you can confidently look up data in any worksheet.

Bottom note: Information gathered from public forums or data available on the internet and portrayed here.