Match value in 2 different sheets with a formula in an excel report

In some of my reports, I need to know if a group, a CI or a condition is matching with another sheet so I can take the appropriate action.

macro excel macro excel


When I use the formula ?

To know if 2 values are identical in 2 different sheets.


How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".


How are the formulas ?




Put this formula in sheet2 (column B):

=IF(ISNA(VLOOKUP(A2,Sheet1!A:A,1,0)),"no match","yes match")

Alternatively, I can use this simple formula (column C):

=IF(A2=Sheet1!A2,"yes match","no match")

The only difference and it is the most important is that with vlookup, it doesn’t matter the order because it will search anywhere in the column. For the second formula, in both sheets, it is mandatory that the order is the same.

Vlookup is a very useful function to look for the corresponding value, if you want to know more, read Vlookup, a quick way to find the value of a condition in an excel report.

Interesting Management