Excel Formula to Compare Values in Two Tables

Overview

This document outlines an approach for comparing values in two separate tables in Excel. The example provided will demonstrate how to identify discrepancies between the two datasets.

Scenario

Assume you have two tables:

• Table1 located in the range A2:B10, with columns as follows:
  • A: Item Names
  • B: Item Prices
• Table2 located in the range D2:E10, with the same structure:
  • D: Item Names
  • E: Item Prices

To compare the prices of items in Table1 with those in Table2, we will create a formula in Column C of Table1 that checks if the item prices match.

Excel Formula

The following formula can be used in cell C2 of Table1 to compare the prices:

=IF(B2=VLOOKUP(A2, D:E, 2, FALSE), "Match", "Mismatch")

Explanation of the Formula Components

1. B2: This refers to the price of the item in Table1 that you want to compare.
2. VLOOKUP(A2, D:E, 2, FALSE):
   • A2: This is the item name in Table1 for which we are looking for a price in Table2.
   • D:E: This is the range of the second table where the VLOOKUP function will search for the item.
   • 2: This indicates that you want to retrieve the price from the second column of the range (Column E).
   • FALSE: This specifies that an exact match is required.
3. IF(B2=..., "Match", "Mismatch"): This portion of the formula checks if the price from Table1 (B2) matches the price retrieved by VLOOKUP from Table2. If they match, "Match" is returned; otherwise, "Mismatch" is returned.

Practical Example

Steps:

1. Enter the formula =IF(B2=VLOOKUP(A2, D:E, 2, FALSE), "Match", "Mismatch") in cell C2 of Table1.
2. Drag the formula down from C2 to C10 to apply it to all items.
3. The output in Column C will display "Match" for Apple and Orange, and "Mismatch" for Banana, indicating discrepancies in prices.

Conclusion

This method provides a straightforward way to compare values in two tables using Excel's VLOOKUP function and conditional logic. For users seeking to deepen their analytical skills, consider exploring courses on the Enterprise DNA Platform focusing on advanced Excel techniques and data analysis.