How to Use VLOOKUP in Excel – A Step-by-Step Guide

The VLOOKUP function in Excel is one of the most powerful and commonly used tools for searching and retrieving data from a table. Whether you're managing a product list, student scores, or customer records, VLOOKUP makes it easier to find matching data across spreadsheets.

✅ What is VLOOKUP?

VLOOKUP (Vertical Lookup) searches for a value in the first column of a range or table and returns a value in the same row from a different column.

🔧 VLOOKUP Formula Syntax

    =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 

•     lookup_value – The value to search for.
•     table_array – The range of cells that contains the data.
•     col_index_num – The column number in the table from which to retrieve the value.
•     range_lookup – Optional. Use FALSE for exact match, TRUE for approximate match.

📘 Step-by-Step: How to Use VLOOKUP

🖼 Step 1: Open Your Data

Start by opening your Excel file. Here we have a simple table with two columns: Product ID and Product Name. Here we want to bring Code from "Item Code" sheet by matching id.

 Item Code worksheet- 

 

🖼 Step 2: Prepare the Lookup Value

Select all the data row than give a table_array name – The range of cells that contains the data.

🖼 Step 3: Write the VLOOKUP Formula

Use the VLOOKUP formula like this:

=VLOOKUP(A2,itemcode,2,FALSE)

A2 is the cell where the Product ID is typed.
itemcode is the range containing the product table.
2 means we want to return the value from the second column (Code).
FALSE specifies we want an exact match.

🖼 Step 4: Get the Result

Enter vlookup formula in the C2 column and press enter. After pressing Enter, Excel will return the corresponding Code if found.

🖼 Step 5: Handle Errors

If the Product ID is not found, VLOOKUP will return #N/A. You can handle this using the IFERROR function:

=IFERROR(VLOOKUP(A2, itemcode, 2, FALSE), "Not Found")

This will display “Not Found” instead of #N/A.

🧠 Tips for Using VLOOKUP

• Always make sure the lookup column (first column in the table) is sorted or has unique values for better accuracy.

• Use $ signs to make ranges absolute if you're copying the formula down multiple rows.

• Consider using XLOOKUP or INDEX + MATCH for more advanced lookups (available in newer Excel versions)

---

✅ Summary

VLOOKUP is a handy Excel function to pull data from tables. By following the steps with screenshots above, you can quickly start using it to enhance your data analysis and reporting.