20. Excel How to compare two columns ? How do I fill in one sheet from another ?


Comparison of two columns

Consider the following spreadsheet with a list of employees:

Excel 2016 Example Comparison of two columns

We want to fill in column E "Manager" with the word "Yes" for the managers of the organisation. The list of managers is written in another sheet "Sheet2":

Excel 2016 Example Comparison of two columns

I assume that the two sheets belong to the same workbook, and that column A "Name" does not contain any duplicates.

The formula to be written in cell E2 and copied to column E by Autofill :

=IF(COUNTIF(Sheet2!$A$2:$A$179,A2)>0,"Yes","")

Completing one sheet from another

Using the VLOOKUP function

In the previous example, we added "Yes" for the organisation's managers. We now want to add the responsibility position for each manager by extracting it from column B of the sheet "Sheet2".

To do this we will use the formula :

=VLOOKUP(A2,Sheet2!$A$1:$B$179,2,FALSE)

But as we can see from the following extract, the result is not satisfactory:

Excel 2016 Example Comparison of two columns

Indeed, the function VLOOKUP returns the error #N/A when the searched value is not found.

The solution is to use the IFERROR function. This function returns the value of its 1st argument if it is not an error. Otherwise, the function returns the value of its 2nd argument. So, in this case, I give the empty string "" as the 2nd argument.

The formula in F2 to be copied to column F by Autofill is :

=IFERROR(VLOOKUP(A2,Sheet2!$A$1:$B$179,2,FALSE),"")

The result:

Excel 2016 Example Comparison of two columns

Using the MATCH and INDEX functions

The same problem can be solved using the formula :

=INDEX(Sheet2!$A$2:$B$179,MATCH(A2,Sheet2!$A$2:$A$179,0),2)

The MATCH function in this formula returns the position of the value in A2 in the range A2:A179 of the sheet "Sheet2".

INDEX returns the value in the range A2:B179 of the sheet "Sheet2" in the intersection of

  • the row whose number is the value returned by MATCH and
  • column 2.

But, once again, the MATCH function returns the error #N/A when the searched value is not found.

In order not to have this error displayed, use preferably the formula :

=IFERROR(INDEX(Sheet2!$A$2:$B$179,MATCH(A2,Sheet2!$A$2:$A$179,0),2),"")


<< 19. Excel How to count the number of unique values ?

21. Excel How to count cells with a specific text ? >>