How to compare two columns and return a value from a third in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7,"No match")Look each value up in the other list and return the column you need: =XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7,"No match"), filled down.
- Result on the example
- one result per row in
B2, filled down toB7 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.
How it works
For each badge in column A, XLOOKUP searches the badge IDs in column D and returns the name from column E on the matching row. B-4499 isn't in the list, so it shows "No match". The $ signs keep the reference list fixed as the formula fills down.
=XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7,"No match")Result"Aisha Khan"
A2"B-4471"The value in A2.
$D$2:$D$76 rows × 1 col: "B-4402", "B-4418", "B-4431", "B-4447", …A range: 6 cells in one column.
$E$2:$E$76 rows × 1 col: "Marcus Lee", "Priya Nair", "Tom Becker", "Rosa Diaz", …A range: 6 cells in one column.
XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7,"No match")"Aisha Khan"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
A2"B-4471" - lookup_array
$D$2:$D$76 rows × 1 col: "B-4402", "B-4418", "B-4431", "B-4447", … - return_array
$E$2:$E$76 rows × 1 col: "Marcus Lee", "Priya Nair", "Tom Becker", "Rosa Diaz", … - if_not_found
"No match"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7,"No match"). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IFNA(VLOOKUP(A2,$D$2:$E$7,2,FALSE),"No match") | Aisha Khan, Priya Nair, Marcus Lee… | Works in every version. IFNA replaces only the not-found error. |
| =IFNA(INDEX($E$2:$E$7,MATCH(A2,$D$2:$D$7,0)),"No match") | Aisha Khan, Priya Nair, Marcus Lee… | The INDEX/MATCH version, for when the name column is to the left of the IDs. |
Common mistakes
- Without the
$, the reference list moves down one row per row and later lookups miss. - This compares each value with the whole other column. To compare row by row (is A2 the same as D2?), use
=IF(A2=D2,E2,"").
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Compare two columns and return a value from a third
B2, return the employee name for the badge in column A by matching it against D2:D7, or "No match". Fill down to B7.Questions
How do I return Yes or No instead of a value?
Test whether it's found: =IF(COUNTIF($D$2:$D$7,A2),"Yes","No").
How do I do this in Google Sheets?
The same XLOOKUP works, or =IFNA(VLOOKUP(A2,$D$2:$E$7,2,FALSE),"No match").
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.