# How to compare two columns and return a value from a third in Excel

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.

**Excel:** `=XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7,"No match")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Badge scan | Name |  | Badge ID | Employee |
| B-4471 |  |  | B-4402 | Marcus Lee |
| B-4418 |  |  | B-4418 | Priya Nair |
| B-4402 |  |  | B-4431 | Tom Becker |
| B-4499 |  |  | B-4447 | Rosa Diaz |
| B-4431 |  |  | B-4459 | Ken Watanabe |
| B-4459 |  |  | B-4471 | Aisha Khan |

Formula in B2, filled to B7: `=XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7,"No match")`

Result: Aisha Khan; Priya Nair; Marcus Lee; No match; Tom Becker; Ken Watanabe

## 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.

## Other ways

- `=IFNA(VLOOKUP(A2,$D$2:$E$7,2,FALSE),"No match")`: 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")`: 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,"")`.

## 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")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/compare-two-columns-return-third