如何在Excel中实现两表ID匹配的名称校验及未匹配提示？

To get the exact result you're looking for, you can combine lookup functions with logical checks. Below are two approaches depending on your Excel version:

Using XLOOKUP (Recommended for Excel 365/2021+) #

Assuming your Table1 is in columns A (ID) and B (Name), and Table2 is set up as an Excel Table named Table2 with columns ID and Name, enter this formula in cell C2 (the first Result cell for Table1):

=IFERROR(IF(XLOOKUP(A2, Table2[ID], Table2[Name])=B2, "Name match", "Name mismatch"), "ID Not found")

How this works: #

• XLOOKUP(A2, Table2[ID], Table2[Name]): Fetches the corresponding Name from Table2 using the ID in cell A2.
• IF(..., "Name match", "Name mismatch"): Compares the fetched Name from Table2 with the Name in Table1 (cell B2) and outputs the appropriate match status.
• IFERROR(..., "ID Not found"): Handles cases where the ID from Table1 doesn't exist in Table2, replacing the default #N/A error with your desired message.

Using VLOOKUP (For Older Excel Versions) #

If you're using an Excel version without XLOOKUP support, use this alternative formula (replace $D$2:$E$4 with your actual Table2 range):

=IFERROR(IF(VLOOKUP(A2, $D$2:$E$4, 2, FALSE)=B2, "Name match", "Name mismatch"), "ID Not found")

How this works: #

• VLOOKUP(A2, $D$2:$E$4, 2, FALSE): Performs an exact match lookup for the ID in A2 within the first column of Table2, returning the value from the second column (Name).
• The IF and IFERROR logic follows the same logic as the XLOOKUP version to output match status or "ID Not found".

Quick Tip: #

If you drag the formula down to apply it to all rows in Table1, make sure to use absolute references (like $D$2:$E$4 in the VLOOKUP example) to prevent the Table2 range from shifting incorrectly.

内容的提问来源于stack exchange，提问作者Istiak Mahmood