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)
| 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!
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.