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.
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 is/are the formula(s) ?
=IF()
=ISNA()
=VLOOKUP()
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 Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...