Lookup & Reference
Reverse Left Lookup: Retrieve Values to the Left of the Lookup Column
Look up an ID or SKU positioned to the left of the search column without rearranging worksheet columns using XLOOKUP or INDEX and MATCH.
Overcome VLOOKUP's rightward-only column constraint
Classic VLOOKUP always searches the leftmost column of table_array and cannot use a negative col_index_num to look leftward. Rearranging source columns just to satisfy VLOOKUP risks breaking downstream formulas.
Separating the lookup vector from the return vector via XLOOKUP(key, C2:C5, A2:A5, "Not Found") — or IFNA(INDEX(A2:A5, MATCH(key, C2:C5, 0)), "Not Found") for older workbooks — retrieves columns in any direction safely.
| Emp_ID (Col A — Return) | Department (Col B) | Work Email (Col C — Search) | Left Lookup Result |
|---|---|---|---|
| EMP-301 | Finance | nora@acme.io | — |
| EMP-302 | Platform | ivan@acme.io | — |
| EMP-303 | Security | maya@acme.io | Matches -> EMP-303 |
| EMP-304 | Operations | leo@acme.io | — |
Modern Left Lookup with XLOOKUP and Built-In Fallback
Searches email column C2:C5 for B7 and returns the corresponding employee ID from A2:A5.
=IF(ISBLANK(B7), "Enter Email", XLOOKUP(TRIM(B7), C2:C5, A2:A5, "Email Not Found"))Backward-Compatible INDEX & MATCH Left Lookup
Produces the identical leftward lookup in legacy spreadsheet versions without XLOOKUP.
=IF(ISBLANK(B7), "Enter Email", IFNA(INDEX(A2:A5, MATCH(TRIM(B7), C2:C5, 0)), "Email Not Found"))Try the example
Use the original values or change them to test your understanding.