VLOOKUP Formula Examples: Real Spreadsheet Uses

VLOOKUP in Excel and Google Sheets searches for a value in the first column of a table and returns a matching value from another column in the same row. You enter the lookup value, the table range, the column number to pull from, and whether you want an exact or approximate match. Below are clear examples of the most common VLOOKUP formulas, plus the logic behind each one.

How VLOOKUP Works

VLOOKUP stands for “vertical lookup.” It scans down the first column of a range you specify. When it finds the lookup value, it moves horizontally to the column number you give it and returns that cell’s content. The function has four arguments:

• lookup_value – what you are searching for (a number, text, or cell reference).
• table_array – the range that contains the data, with the lookup column as the first column.
• col_index_num – the column number in the range from which to return a value (1 is the first column).
• range_lookup – TRUE for approximate match (requires sorted data), FALSE for exact match.

The syntax is: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). In Google Sheets, the same syntax works. If you omit the fourth argument, it defaults to TRUE, which can cause unexpected results if your data is not sorted.

Exact Match Example

Suppose you have a product table in cells A2:C10, with Product ID in column A, Product Name in column B, and Price in column C. To find the price of the product with ID “P-104”, you would write:

=VLOOKUP("P-104", A2:C10, 3, FALSE)

This searches for “P-104” in column A and returns the price from column C (the 3rd column of the range). The FALSE ensures you get an exact match; if the ID does not exist, the formula returns #N/A.

Approximate Match Example

Approximate match is useful for finding a value within a range, like tax brackets or discount tiers. For example, a shipping cost table lists weight thresholds in column A (0, 5, 10, 20) and shipping fees in column B. To find the fee for a 7 kg package, use:

=VLOOKUP(7, A2:B5, 2, TRUE)

VLOOKUP will find the largest weight that is less than or equal to 7 (which is 5) and return the corresponding fee. The first column must be sorted in ascending order for this to work correctly.

Using a Cell Reference as the Lookup Value

Instead of typing a value directly, you can reference a cell. If cell D2 contains the employee ID you want to look up, and your employee table is in A2:B10, the formula becomes:

=VLOOKUP(D2, A2:B10, 2, FALSE)

This is the most common way to use VLOOKUP in real spreadsheets, because you can copy the formula down a column and each row will look up its own value. Make sure to use absolute references (like $A$2:$B$10) if you plan to copy the formula, so the range does not shift.

Common Errors and How to Fix Them

• #N/A – The lookup value is not found. Check for extra spaces, different data types (text vs. number), or that the value actually exists.
• #REF! – The col_index_num is greater than the number of columns in the table range. For example, if your range is A2:C10 and you ask for column 4, you get #REF!.
• #VALUE! – The col_index_num is less than 1, or the lookup value is not compatible with the first column.
• Wrong results with TRUE – If you use approximate match, the first column must be sorted ascending. Otherwise, VLOOKUP may return a random value.
• Leading or trailing spaces – Use =TRIM() on both the lookup value and the first column to remove invisible spaces.

VLOOKUP vs. XLOOKUP vs. INDEX-MATCH

VLOOKUP has limitations: it can only look to the right (it cannot return a value from a column to the left of the lookup column), and it breaks if you insert a column in the table. XLOOKUP (available in newer Excel and Google Sheets) solves both issues. INDEX-MATCH is an older alternative that also works leftward. For example, to return a value from column A based on a match in column C, you would use:

=INDEX(A2:A10, MATCH("target", C2:C10, 0))

This combination is more flexible but longer to write. For most simple lookups, VLOOKUP is still perfectly fine.

Real-World Example: Employee Directory

Imagine an employee table in A2:D10:

• Column A: Employee ID (e.g., 101, 102)
• Column B: Full Name
• Column C: Department
• Column D: Email

To get the department for employee 103, you write:

=VLOOKUP(103, A2:D10, 3, FALSE)

To get the email, change the column index to 4. This is a typical use case for HR spreadsheets or event planning lists.

Frequently Asked Questions

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

Often it is because the lookup value is formatted as text while the first column contains numbers (or vice versa). Another common cause is extra spaces. Use =TRIM() on both sides, or convert the data types with =VALUE() or =TEXT().

Can VLOOKUP return multiple columns?

No, a single VLOOKUP returns only one value. To return multiple columns, write separate VLOOKUP formulas for each column index, or use XLOOKUP with an array return. In Excel 365, you can also use =VLOOKUP(...) inside a dynamic array formula.

Does VLOOKUP work with wildcards?

Yes, for exact match (FALSE) you can use wildcards in the lookup value: an asterisk (*) for any sequence of characters and a question mark (?) for a single character. For example, =VLOOKUP("A*", ...) finds the first value starting with “A”.

How do I use VLOOKUP across different sheets?

Reference the sheet name in the table array, like =VLOOKUP(D2, Sheet2!$A$2:$B$10, 2, FALSE). If the sheet name contains spaces, put it in single quotes: ='Data Sheet'!$A$2:$B$10.

What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP searches vertically (down a column), while HLOOKUP searches horizontally (across a row). Use HLOOKUP when your lookup value is in the first row of a table and you want to return a value from a lower row.

Can I use VLOOKUP to compare two lists?

Yes. Use VLOOKUP to check if each item in list A appears in list B. For example, =IF(ISNA(VLOOKUP(A2, B:B, 1, FALSE)), "Not Found", "Found"). This is a common way to find duplicates or missing entries.

Related

• BL450ANZMN 30 Power Safety & Buying Facts
• How to Get Spooky Tokens in Blox Fruits
• Why Your Playtime Is Longer Than a Game’s Story Length Estimate

in id | Employee ID | - | 104 | 1
in name | Employee Name | - | Maria | 0
out lookup | Matched Name | - | id | 0