# 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:

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":

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:

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 1^{st} argument if it is not an error. Otherwise, the function returns the value of its 2^{nd} argument. So, in this case, I give the empty string "" as the 2^{nd} 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:

*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),"")