How to look up a value to the left in Excel

Why VLOOKUP cannot look left

The staff list has the badge ID in the last column, D. Someone hands you badge B-2207 and wants the name. VLOOKUP always searches the first column of the table you give it and returns a column counted to the right of it. With the table starting at D, there is nothing to the right to return. A table starting at A searches the names, not the badges.

A negative column number does not reverse the direction. It returns #VALUE!, because the column number must be 1 or more.

Method 1: XLOOKUP (Excel 365, 2021 and later)

XLOOKUP takes the column to search and the column to return as two separate ranges, so their order on the sheet does not matter. Search D2:D9, return A2:A9. It is an exact match by default, so there is no FALSE to forget.

Add a fourth argument for a friendlier result when the badge does not exist: =XLOOKUP("B-9999",D2:D9,A2:A9,"No such badge") returns No such badge instead of #N/A. Change the return range to C2:C9 to get the start date, Jan 10, 2022 once formatted as a date. To bring back several columns at once, give it a wider return range; the guide on returning multiple columns covers that.

Method 2: INDEX and MATCH (every version)

MATCH finds the position of the badge in D2:D9: B-2207 is third. INDEX returns the third cell of A2:A9. Because the two ranges are separate, INDEX can point left, right or at another sheet. The 0 at the end of MATCH means exact match; leave it out and MATCH does an approximate match, which on unsorted IDs returns wrong names.

Use this when anyone opening the file has Excel 2019 or older, where XLOOKUP returns #NAME?. For a "not found" message, wrap it: =IFERROR(INDEX(A2:A9,MATCH("B-9999",D2:D9,0)),"No such badge").

The gotcha: ranges that start on different rows

Both methods return the item at the same position in the return range as the match in the search range. If the ranges start on different rows, you get the wrong row with no error. Here the return range includes the header row, so position 3 is A3, Sam Patel, not Jo Kim.

This happens when you select a column with its header for one range and without it for the other, or when you fill a formula down without $ signs and the ranges slide. Press F2 and check that the coloured boxes start and end on the same rows. Ranges of different sizes give #VALUE! in XLOOKUP but INDEX/MATCH can still return a wrong answer, so check INDEX/MATCH especially.

Other workarounds, and why to skip them

Move the column. If you own the sheet, cutting the badge column and inserting it before the names lets a plain VLOOKUP work. That is fine for a one-off, but other people's formulas and reports may depend on the column order, and VLOOKUP's column number breaks again whenever someone inserts a column inside the table.

The CHOOSE trick. Older forum answers build a reordered table on the fly: =VLOOKUP(G1,CHOOSE({1,2},D2:D9,A2:A9),2,FALSE). It is valid Excel, but it is hard to read and harder to edit, and INDEX/MATCH does the same job in every version. In Excel 365, HSTACK(D2:D9,A2:A9) can replace CHOOSE, but XLOOKUP is still shorter.

A helper column. Add a column to the right of the badges that repeats the name with =A2, then VLOOKUP into it. It works, but it duplicates data that can go stale.

Duplicates: which match comes back

Every method above returns the first match from the top. Looking up the department Sales to the left returns the first Sales person, Jo Kim, even if you wanted the most recent hire. XLOOKUP can search from the bottom with -1 as its sixth argument. To get every match, use FILTER; the guide on returning multiple values shows how.

Which method to pick

Everyone has Excel 365, 2021 or later: XLOOKUP. It is the shortest, has a built-in not-found value and can search from the bottom. The file is shared with people on Excel 2019 or older: INDEX/MATCH, which also keeps working when columns are inserted. Avoid the CHOOSE trick and avoid moving columns in a shared file. Whichever you use, put the value you look up in a cell, as in G1, rather than typing it into the formula.