How to Do a VLOOKUP to the Left (3 Working Methods)

The short answer: VLOOKUP can't look left, but these formulas can

VLOOKUP only returns values from columns to the right of the column it searches. If your ID sits to the right of the name you want back, use INDEX/MATCH (works in every Excel version and in Google Sheets) or XLOOKUP (Microsoft 365 and Excel 2021 or later).

If you want to know how to do a VLOOKUP to the left, these two formulas do the job for the example table below:

INDEX/MATCH:  =INDEX($A$2:$A$6, MATCH(F2, $C$2:$C$6, 0))

XLOOKUP:      =XLOOKUP(F2, $C$2:$C$6, $A$2:$A$6, "Not found")

Both search the ID column (C) for the value in F2 and return the matching name from column A. XLOOKUP needs Microsoft 365 or Excel 2021 or later. It is not in Excel 2019 or earlier, so use INDEX/MATCH there.

Example data and expected results

Here is the sample sheet. The key (Employee ID) is in column C, to the right of the name and department.

Type an ID in F2 and put a formula in G2. Here is what you should see:

Why VLOOKUP fails here

VLOOKUP always searches the first column of the range you give it, then counts rightward to the column you want. The number that does the counting is called the column index.

To search IDs in column C, your range must start at C, and there is nothing to the right of C. Pointing the range at A2:C6 makes VLOOKUP search column A for the ID, so =VLOOKUP(1001, A2:C6, 1, FALSE) returns #N/A. A column index of 0 or less returns #VALUE!. VLOOKUP has no setting for "count leftward."

Method 1: INDEX and MATCH (works everywhere)

=INDEX($A$2:$A$6, MATCH(F2, $C$2:$C$6, 0))

How MATCH and INDEX each work

MATCH finds where something sits in a list. It takes three arguments:

• F2: the value you're looking for (the ID).
• $C$2:$C$6: the single column to search.
• 0: exact match. Without it, MATCH assumes your list is sorted and can return wrong answers on unsorted data.

For 1001, MATCH returns 4, because 1001 is the fourth item in C2:C6.

INDEX returns the item at a given position in a range. It takes two arguments here:

• $A$2:$A$6: the column you want a value from (the names).
• The position, supplied by MATCH. The 4th item in A2:A6 is Tom Nguyen.

Because the search column and return column are separate arguments, the return column can be anywhere, left or right. That is the whole trick.

Build it step by step

1. In F2, type the ID you want to look up, such as 1001.
2. In G2, type =MATCH(F2, $C$2:$C$6, 0) and check it returns 4. This confirms the search half works.
3. Wrap it: =INDEX($A$2:$A$6, MATCH(F2, $C$2:$C$6, 0)).
4. Fill the formula down. The $ signs (absolute references) lock the ranges so they don't slide as you copy; F2 has no $, so it moves to F3, F4 and so on.

One bonus over VLOOKUP: INDEX/MATCH refers to columns directly, not by counting. If someone inserts a column in your table, a VLOOKUP's hard-coded column number can quietly point at the wrong column. INDEX/MATCH adjusts automatically.

Method 2: XLOOKUP (Microsoft 365, Excel 2021+)

=XLOOKUP(F2, $C$2:$C$6, $A$2:$A$6, "Not found")

• F2: what you're looking for.
• $C$2:$C$6: where to look.
• $A$2:$A$6: what to return from the matching position.
• "Not found": optional text shown instead of #N/A.

XLOOKUP matches exactly by default, so you don't need a trailing 0 or FALSE. It also looks in either direction, which is why left lookups need no workaround. Microsoft documents all its options in its XLOOKUP function article.

Availability: XLOOKUP is not in Excel 2019, 2016 or earlier. If a coworker opens your file in one of those versions, the formula breaks, so use INDEX/MATCH when files are shared widely. Google Sheets also has an XLOOKUP function, but check Google's current function list before relying on it.

For a deeper comparison, see XLOOKUP vs VLOOKUP: Which Should You Use?

Method 3: VLOOKUP with CHOOSE (legacy workaround)

=VLOOKUP(F2, CHOOSE({1,2}, $C$2:$C$6, $A$2:$A$6), 2, FALSE)

CHOOSE({1,2}, ...) builds a temporary two-column table in memory. The {1,2} is an array constant, meaning a fixed list of values written inside curly braces. It tells CHOOSE to return both choices side by side: first the ID column, then the name column.

VLOOKUP now sees IDs in its first column and names in its second, so 2 and FALSE (exact match) work as usual. For 1001 it returns Tom Nguyen.

Caveats:

• Harder to read and audit. Most people reviewing the sheet will need a minute to understand it.
• Older Excel versions may need you to confirm the formula with Ctrl+Shift+Enter instead of Enter.
• Google Sheets may need the formula wrapped in ARRAYFORMULA(...).
• It rebuilds the temporary table for each formula, which isn't worth it on big sheets.

Use this only if you must keep the VLOOKUP style. Otherwise pick Method 1 or 2.

Which method should you use?

Variations

Show a friendly message instead of #N/A

Wrap INDEX/MATCH in IFNA, which replaces only #N/A errors:

=IFNA(INDEX($A$2:$A$6, MATCH(F2, $C$2:$C$6, 0)), "Not found")

IFERROR works too, but it hides every error type, including mistakes in your formula. IFNA is safer. With XLOOKUP, the fourth argument does this job.

Look up across another sheet

Add the sheet name before each range: =INDEX(Staff!$A$2:$A$6, MATCH(F2, Staff!$C$2:$C$6, 0)). If the sheet name has spaces, wrap it in single quotes, like 'Staff List'!$A$2:$A$6.

Return a different column

To return the Department for the same ID, change only the INDEX range: =INDEX($B$2:$B$6, MATCH(F2, $C$2:$C$6, 0)). ID 1001 returns Sales.

Wildcards

With match type 0, MATCH accepts * (any characters) and ? (one character) in text lookups. =MATCH("Mar*", A2:A6, 0) returns 1 here, the first name starting with "Mar". Wildcards only work on text, not numbers.

Common errors and fixes

#N/A when the ID looks right

• Extra spaces. An ID typed as "1001 " won't match "1001". Clean it with TRIM(F2) in the formula.
• Text vs number. The ID in one place may be stored as text and in the other as a number. Both look the same, but they don't match. Text values often sit left-aligned, and Excel may show a green triangle. Convert text to a number with VALUE(F2), or multiply by 1. Or use Data > Text to Columns on the column and click Finish.
• Wrong range. Check that the search range covers the column containing the IDs.

A wrong result, not an error

• Ranges differ in size or start in different rows. $A$2:$A$6 with $C$3:$C$7 is misaligned, so positions point at the wrong rows. Keep both ranges the same height, starting on the same row.
• Missing the 0. Leaving off the exact-match argument in MATCH can return a wrong row on unsorted data.

Duplicates return only the first match

If ID 1001 appears twice, INDEX/MATCH and XLOOKUP return only the first one. To see all matches, use FILTER (Microsoft 365, Excel 2021+, and Google Sheets), for example =FILTER($A$2:$A$6, $C$2:$C$6=F2). On older Excel, a pivot table or Data > Filter works.

Results shift when you copy down

If your ranges lack $ anchors, they slide down one row per copy and eventually miss the data. Use $A$2:$A$6 style for the table ranges, and leave the lookup cell relative.

Semicolons instead of commas

This article uses commas between arguments, the US convention. Some regional settings use semicolons, so MATCH(F2; C2:C6; 0). If Excel rejects a formula you typed, try semicolons.

Excel vs Google Sheets

INDEX/MATCH works the same in both. It's the safest choice if a file moves between them. XLOOKUP is available in Google Sheets as well, but confirm in Google's function list.

The CHOOSE trick may need ARRAYFORMULA in Sheets. Whole-column references like C:C are common in Sheets and work in Excel too, but they can be slower on large workbooks, so bounded ranges are tidier.

When to restructure instead

Sometimes a formula is a patch for a layout problem.

• If you own the data, move the key column to the left. Then plain VLOOKUP works, and anyone can read it.
• Convert the range to an Excel Table (Ctrl+T). Formulas then use names like Staff[Employee ID] instead of cell addresses, which are easier to read and grow with the data.
• For big or repeated joins, use Power Query (Data > Get Data in Excel) to merge two tables. It lets you pick the matching columns in any order and refresh with one click. For very large datasets, a database is the better tool.

Next steps

• Confirm your key and return columns, then pick INDEX/MATCH for shared files or XLOOKUP for modern Excel.
• Anchor the ranges with $.
• Check for stray spaces and text-formatted numbers if you see #N/A.
• Add IFNA or XLOOKUP's not-found text before sharing the sheet.