VLOOKUP in Excel: Return a Value from Another Cell

To make VLOOKUP return a value from another cell, look up a key in the leftmost column of a table and specify the column containing the value you want. For example, with the lookup value in E2 and a table in A2:C6, =VLOOKUP(E2,A2:C6,3,FALSE) finds E2 in column A and returns the matching value from column C.

What VLOOKUP Returns

VLOOKUP searches vertically down the first column of a selected range. When it finds a matching item, it returns a value from the same row in another column of that range. The returned result appears in the cell containing the formula; VLOOKUP does not move, copy, or reformat the original cell.

For example, imagine a product list with product codes in column A, descriptions in column B, and prices in column C. If you enter a product code in E2, a VLOOKUP formula can find that code and show its price in F2. The result updates when you change the code in E2, which makes the formula useful for lookup fields, reports, and order forms.

VLOOKUP returns the first matching row it encounters. It returns a value, not the address of the matching cell or the formula that may be stored there. If the matching return cell contains a formula, Excel returns that formula cell’s calculated result.

How the Formula Works

The function has four arguments: the value to find, the table range, the number of the return column within that range, and whether to use an exact or approximate match. The column number is counted from the range’s left edge, not from the worksheet’s column letters.

Suppose A2:C6 contains product codes, names, and prices, with P-104 in A5 and 18.50 in C5; E2 contains P-104. This formula returns 18.50:

With product codes in A2:A6, product names in B2:B6, prices in C2:C6, and P-104 in E2, =VLOOKUP(E2,A2:C6,3,FALSE) returns the price 18.50 from C5.

Here, E2 is the lookup value, A2:C6 is the table array, 3 means the third column of that range, and FALSE requests an exact match. Since the first column of A2:C6 is worksheet column A, the third column is worksheet column C. If you changed the table range to B2:C6, a column number of 3 would be outside the range.

Set Up a VLOOKUP Step by Step

Place the item you want to find in a cell, such as E2. The lookup value may be typed directly into the formula cell or referenced from another cell.
Select a table range whose leftmost column contains the lookup values. Include the column you want to return, as well as any columns between it and the lookup column.
Count across the selected range to determine the return-column number. The leftmost column is 1, the next is 2, and so on.
Use FALSE as the fourth argument when you need an exact match, which is the normal choice for codes, names, and identifiers.
Enter the formula in the cell where you want the answer to appear, then press Enter. In Windows, F2 edits the selected cell and Ctrl+Enter enters the same value or formula into all selected cells. On Mac, use Control+U to edit a cell and Command+Return to fill the selected range.

With codes in A2:A6, descriptions in B2:B6, and P-104 in E2, =VLOOKUP(E2,A2:B6,2,FALSE) returns the description from B5. The formula is entered in the destination cell, for example F2; it does not have to be entered next to the source table.

Return a Column Chosen in Another Cell

Sometimes the lookup key is in one cell and the desired return-column number is in a separate cell. You can use that second cell as VLOOKUP’s third argument. This is useful when a report lets a user choose which field to display, but the chosen number must still refer to a valid column in the selected table.

With product data in A2:C6, the key P-104 in E2, and the number 3 in H1, =VLOOKUP(E2,A2:C6,H1,FALSE) returns 18.50 from the third column of the matching row. If H1 contains 2 instead, the same formula returns the product name from column B. H1 must contain a valid column number within the range, from 1 through 3 in this example.

This is different from asking VLOOKUP to return a cell address. VLOOKUP returns the value held in the selected column. If you want the formula to display a different field, change its return-column number or the cell that supplies that number.

Exact Match Versus Approximate Match

The fourth argument controls how Excel searches. FALSE means exact match: Excel looks for a value equal to the lookup value and returns #N/A if it cannot find one. TRUE means approximate match. If the argument is left out, VLOOKUP uses approximate matching by default, so include FALSE explicitly for ordinary lookups.

Approximate matching is intended for ordered ranges, such as tax bands, commission thresholds, or grading cutoffs. The first column must be sorted in ascending order for reliable results. Excel finds the largest value that is less than or equal to the lookup value; it does not require an exact match. For an unsorted list of product codes, approximate matching can return an incorrect row without an obvious warning.

For a table of exact text or numeric identifiers, use FALSE. Exact matching is not case-sensitive, so codes that differ only by capitalization are treated as the same lookup value. With FALSE, wildcard characters can also have special meaning in text lookups: an asterisk matches a sequence of characters, a question mark matches one character, and a tilde can escape a wildcard when you need to search for it literally.

Common Problems and How to Fix Them

#N/A: The lookup value may not exist in the first column, or the key and table values may differ in type or content. Check for leading or trailing spaces, inconsistent punctuation, and numbers stored as text. Confirm that the lookup value is in the range’s leftmost column.
#REF!: The return-column number is larger than the number of columns in the table range, or the referenced range was changed or deleted. Recount columns from the selected range’s left edge.
Wrong result: Check whether the fourth argument is FALSE. If it is omitted or TRUE, Excel may perform an approximate search. Also check for duplicate keys: VLOOKUP returns the first match from the top of the range.
Unexpected zero: A return cell that is truly blank can appear as 0 in a lookup result. Check the source cell before assuming the key matched a numeric zero.
Formula copied to other rows: Relative references may shift when you fill a formula down. Use dollar signs to lock a range when needed. With the lookup value in E2 and a fixed table in A2:C6, =VLOOKUP(E2,$A$2:$C$6,3,FALSE) returns the matching price and keeps the table range fixed when copied down.

When troubleshooting, test one known key and inspect the source row directly. A value that looks identical may contain a hidden space, or one version may be stored as text while another is numeric. Formatting alone does not always reveal that difference.

VLOOKUP’s Main Limitation

VLOOKUP can only search the leftmost column of its table array and return a value from a column to its right. It cannot look in column C and return a value from column A using the usual arrangement. Rearrange the data, use another lookup method, or define a range that places the search column first.

Its numeric column index can also become fragile when columns are inserted or the table layout changes: a formula may still be valid but point at a different field than intended. A clear table structure and a quick check of the result help prevent silent errors. For repeated work, structured Excel Tables can make ranges easier to maintain, though the same leftmost-column rule still applies to VLOOKUP.

Alternatives for More Flexible Lookups

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It separates the lookup range from the return range, can search in either direction, and uses exact matching by default. In a sheet with product codes in A2:A6, prices in C2:C6, and P-104 in E2, =XLOOKUP(E2,A2:A6,C2:C6) returns 18.50. This avoids counting a return-column index and does not require the return column to be to the right.

INDEX and MATCH are another option, including in Excel versions that do not have XLOOKUP. MATCH locates the row position of the lookup value, and INDEX returns the value at that position in the chosen result range. With codes in A2:A6, prices in C2:C6, and P-104 in E2, =INDEX(C2:C6,MATCH(E2,A2:A6,0)) returns 18.50. The 0 in MATCH requests an exact match.

For a simple table where the lookup column is on the left and the return column is on the right, VLOOKUP remains easy to read and widely compatible. Choose XLOOKUP for a more direct formula in supported versions, or INDEX and MATCH when you need flexibility while supporting older Excel installations.

Frequently Asked Questions

Can VLOOKUP return a value from a cell to the left?
No. The lookup column must be the first column in the selected range, and VLOOKUP returns from a column to its right. Use XLOOKUP in supported versions or INDEX and MATCH if the return column is left of the lookup column.

Why does VLOOKUP return the wrong value?
Common causes include using TRUE or omitting the match argument, choosing the wrong column index, or having duplicate lookup keys. For identifiers, specify FALSE and verify that the first matching row is the one you want.

Does VLOOKUP update when the source value changes?
Yes. If the formula refers to a source cell, Excel recalculates the result when that cell’s value changes, provided calculation is not set to Manual. The formula returns the current result, not a permanent copy of the source value.

Can VLOOKUP return more than one column?
A standard VLOOKUP returns one column for each formula. You can place separate formulas beside one another and use different column numbers, or use a function such as XLOOKUP when its available options suit the task.

What happens if there are duplicate lookup values?
VLOOKUP returns the first matching entry, searching from the top of the first column downward. If you need a later match or all matching rows, a different approach is needed; VLOOKUP is designed to return a single result.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.