lookup value in Excel

Question

I have 2 table.

Table 1

ID Name
1  AAA
2  BBB
3  CCC

Table 2

ID Name
1  AAC
2  BBB
4  DDD  

As output, I would like to to find out -

when the ID matches between 2 tables do we have the same Name there, if so the Output should be Name Match or Name mismatch. Else if the ID is not found between those 2 tables, the output should be "ID Not found"

Expected result -

ID Name Result
1  AAA  Name mismatch
2  BBB  Name match
3  CCC  ID Not found

Anyone knows which query I should use in Excel to get the expected result!

Answer 1

use:

=IF(ISNUMBER(MATCH(A2,E:E,0)),IF(COUNTIFS(E:E,A2,F:F,B2),"Name Match","Name Mismatch"),"ID Not Found")

---

Or this shorter formula:

=IFERROR(IF(VLOOKUP(A2,E:F,2,FALSE)=B2,"Name Match","Name Mismatch"),"ID Not Found")