VLOOKUP in Excel

VLOOKUP in Excel looks up a value in the first column of a table and returns a matching value from another column in the same row. The formula is =VLOOKUP(lookup_value, table_array, col_index_num, FALSE), where FALSE forces an exact match. It's one of the most-used Excel functions in office jobs, because it pulls data from one sheet into another without manual copy-paste.

What VLOOKUP does (and why you'll use it)

Say you have an employee list on one sheet and you type an employee ID somewhere else. VLOOKUP finds that ID in your list and brings back the name, department, or salary sitting next to it. Instead of scrolling through 500 rows, you get the answer in one cell.

The "V" stands for vertical, because it searches down a column.

The four parts of VLOOKUP

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

1. lookup_value — what you're searching for (e.g. an employee ID).
2. table_array — the range that holds your data. The lookup value must be in the first column of this range.
3. col_index_num — which column number to return the answer from, counting from the left of your table.
4. range_lookup — type FALSE for an exact match, TRUE for an approximate match. Beginners should use FALSE almost every time.

Step by step: use VLOOKUP for the first time

Suppose columns A to C hold a list: Employee ID, Name, Salary. You want to find a salary by ID.

1. Click the cell where you want the answer.
2. Type =VLOOKUP(
3. Click the cell holding the ID you're searching for (say E2), then type a comma.
4. Select the table range A2:C100, then type a comma. Press F4 to lock it as $A$2:$C$100 so it doesn't shift when you copy the formula down.
5. Type 3 because Salary is the third column of your table.
6. Type ,FALSE) and press Enter.

Your formula reads: =VLOOKUP(E2,$A$2:$C$100,3,FALSE)

Worked example (Indian salary sheet)

Type EMP102 in cell E2. In F2, enter:

=VLOOKUP(E2,$A$2:$C$4,3,FALSE)

Result: 35,000. Excel matched EMP102 and returned the value from column 3.

VLOOKUP vs XLOOKUP

XLOOKUP is the newer function (Excel 2021 and Microsoft 365). It fixes VLOOKUP's biggest limits: it can look to the left, and you don't count column numbers.

The XLOOKUP version of the salary example:

=XLOOKUP(E2, A2:A4, C2:C4, "Not found")

This says: find E2 in the ID column, return the matching salary from the salary column, and show "Not found" if there's no match.

Pro tips

• Always press F4 to lock the table range ($A$2:$C$100) before copying a VLOOKUP down a column. This is the number-one fix for wrong results.
• Wrap VLOOKUP in IFERROR to hide the ugly #N/A: =IFERROR(VLOOKUP(E2,$A$2:$C$4,3,FALSE),"Not found").
• If you have Microsoft 365, learn XLOOKUP now. It's simpler and less error-prone once you get used to it.

Common mistakes

• Forgetting FALSE. Without it, VLOOKUP does an approximate match and can return the wrong row. Add ,FALSE for exact matches.
• Lookup value not in the first column. VLOOKUP can only search the leftmost column of your table_array. If your ID is in column B, either move it or use XLOOKUP.
• Wrong column number. col_index_num counts from the left edge of your selected table, not from column A of the sheet.
• Extra spaces. EMP102 with a trailing space won't match EMP102. Clean data with TRIM() if lookups fail for no clear reason.

Key takeaways

• VLOOKUP searches down the first column of a table and returns a value from another column in the same row.
• Use FALSE for exact matches almost every time.
• Lock the table range with F4 before copying the formula.
• XLOOKUP (Excel 2021/365) is easier and can search in any direction.

Practice task

Make a two-column table of five products and their prices. In a separate cell, type one product name and write a VLOOKUP that returns its price. Then wrap it in IFERROR so a wrong name shows "Not found".

Want to master Excel for a real office job, including pivot tables and dashboards? The ADCA (AI Integrated) program at HCI covers Excel end to end with hands-on practice.