excelexcel-formulaxlookup

Comparing 2 Excel tables using XLOOKUP function cell by cell


I'm trying to compare 2 tables cell by cell, and what I want to achieve is getting '#N/A' (using XLOOKUP function) in every cell that differs from the first table.

So, I would like to see "#N/A" in cells N8:P10. My function looks like that: =XLOOKUP(I8,C:C,C:C)

enter image description here

| Auto_Inc | Col1    | Col2 | Col3 | Col4 |   | Auto_Inc | Col1    | Col2 | Col3 |    |
|----------|---------|------|------|------|---|----------|---------|------|------|----|
| 1        | Kate    | Do   | Do   | Do   |   | 1        | Kate    | Do   | Do   | Do |
| 2        | Jeremy  | Do   | Do   | Do   |   | 2        | Jeremy  | Do   | Do   | Do |
| 3        | Stan    | Do   | Do   | Do   |   | 3        | Steve   | Do   | Do   | Do |
| 4        | Pavel   | Do   | Do   | Do   |   | 4        | Pavel   | Do   | Do   | Do |
| 5        | Josh    | Do   | Do   | Do   |   | 5        | Josh    | Do   | Do   | Do |
| 6        | Steward | Do   | Do   | Do   |   | 6        | Steward | Do   | Do   | Do |
|          |         |      |      |      |   | 7        | Tim     | Do   | Do   | Do |
|          |         |      |      |      |   | 8        | Alex    | Do   | Do   | Do |
|          |         |      |      |      |   | 9        | Damian  | Do   | Do   | Do |

How can I change it to show correct results?

Thanks for help in advance!


Solution

  • XLOOKUP finds the first occurrence of a value, so Steve returns #N/A as it doesn't appear in the first table, but all your Do values are found as they appear in the first row.

    If you want to do a direct comparison you just need to ask does this cell equal that cell which will return TRUE/FALSE. You can then wrap that in an IF formula to return an #N/A error where the result is FALSE.

    Use the below formula and drag down/across =IF(A2=G2,G2,NA())

    Or
    =IF(G2:K10=A2:E10,G2:K10,NA()) to cover the whole range and spill.