An exact-match example
Enter SO-102 in D2. The source IDs are A2:A4 and owners B2:B4; enter the formula in E2. The expected result is Rin. MATCH returns the position of SO-102 inside the ID range, and INDEX returns the owner at that same position. The final 0 asks MATCH for an exact match.
Use version information
In Microsoft 365 or Excel 2021 you can also use =XLOOKUP(D2,$A$2:$A$4,$B$2:$B$4,"Not found"). XLOOKUP is not available natively in Excel 2016 or 2019. Select the oldest version used by your colleagues when requesting a formula.
Specify the destination sheet
The example assumes all cells are on the same worksheet. For another source sheet, provide its exact name and ranges. Names containing spaces need quoting, for example 'Order data'!$A$2:$A$4. Do not invent a sheet name from the subject of the data.
Worked example
A1:B4
| Order ID | Owner |
|---|---|
| SO-101 | Alex |
| SO-102 | Rin |
| SO-103 | Sam |
=INDEX($B$2:$B$4,MATCH(D2,$A$2:$A$4,0))Teaching example, not a live AI response or an Excel execution record.
Try it step by step
- Paste the example in A1:B4 and type SO-102 in D2.
- Paste the INDEX/MATCH formula into E2 and check for Rin.
- Change D2 to SO-999: the basic formula returns #N/A, which reveals that no match exists.
- Only after confirming that missing IDs are expected, consider IFNA around the lookup for a clear “Not found” message.
Before you rely on the result
- Duplicate IDs return one match, not every record. Decide whether duplicates should be rejected, combined or listed.
- Extra spaces and numeric IDs stored as text can prevent a match. Check both sources before adding error handling.
- Never replace every error with an empty string without understanding why the lookup failed.