The Problem
VLOOKUP has a flaw: it only looks right. If your ID is to the right of the value you want, VLOOKUP fails. INDEX MATCH solves this.
The Easy Way: Use SheetXAI
If you don't want to mess with formulas, the fastest way to do this is simply by asking.
With SheetXAI, you can open the sidebar and type:
Look up the email for these IDs using the data in range D:F.
SheetXAI will instantly write the formula or script for you and fill the cells. It handles the syntax so you can focus on the result.
The Manual Way: The Formulas You Need
To do this manually, you need to use: INDEX and MATCH.
1. MATCH
Finds the row number where a value exists.
Syntax: =MATCH(lookup_value, lookup_array, [match_type])
- lookup_value: The value to find.
- lookup_array: The column to search in.
- [match_type]: 0 for exact match.
Example: Finds that "ID-123" is in row 5.
2. INDEX
Returns the value at a specific row and column.
Syntax: =INDEX(array, row_num, [column_num])
- array: The column containing the return value.
- row_num: The row number to get (calculated by MATCH).
Example: Gets the value from row 5.
Understanding the Logic
- MATCH finds the row number of the ID.
- INDEX goes to that row number in the Email column and returns the text.
The Final Formula:
=INDEX(B:B, MATCH(A2, C:C, 0))
Conclusion
Now you know the "classic" way to solve this using formulas. It's a great skill to have.
But for those times when you just want the job done without the mental math, SheetXAI is there to help.
