vlookup using two columns names in different worksheets

Question

I have one workbook, with two separate worksheets. I want to know if the values that appear in worksheet B also appear in worksheet A, if so, I want to return a "YES". If not, I want to return a "NO". Based on columns A and B.

In worksheet A, I have the following data set:

   NAME             DOB
1  Bob Builder   1/1/2001 
2  Patrik Str    2/2/2001
3  Thunder Ct    3/3/2001
4  peter Grif    4/4/2001

In worksheet B, I have the following data set:

   NAME            DOB
1  Bob Builder   1/1/2001 
2  Patrik Str    2/2/2001
3  Thunder Ct    3/3/2001
4  peter Grif    4/4/2001
5  Bob Builder   8/8/2011 
6  Patrik Str    2/25/2001

I have tried =IFERROR(IF(MATCH(A1,Sheet1!$A:$A,0),"yes",),"no") but it is only looking for column A not B. This works fine only because it looks at column A but there are also other students that have the same name but different DOB.

Can vlookup or match function in excel be used by comparing column name header like name and DOB instead of A, B.

Answer 1

You can use this formula:

=IF(SUMPRODUCT(--('Worksheet A'!$A$2:$A$5='Worksheet B'!A2)*(--'Worksheet A'!$B$2:$B$5='Worksheet B'!B2))=1,"Yes","No")

$A$2:$A$5 the array of name in the first worksheet change it till the last cell in the Name column and $ will fix it so you can drag it without changing
A2 alone is the first name in the second worksheet
$B$2:$B$5 the array of DOB in the first worksheet change it till the last cell in the DOB column and $ will fix it so you can drag it without changing
B2 the corresponding date of A2 in the second worksheet
Sumproduct will result in 1 when both A2 and B2 matches with the corresponding Data
If 1 is the result it will give "Yes" if no match it will give "No"
You can drag the formula down

=IF(SUMPRODUCT(--(Name=A2)*(--DOB=B2))=1,"Yes","No")
If you use Define Name in worksheet A and give Name to the column of Name without the header
Also give DOB for the column of DOB in worksheet A without the header
you can use the above formula and drag it

In case you prefer to use Match use the following:

=IF(ISNA(MATCH(1,(--(Name=A2)*(--(DOB=B2))),0)),"No","Yes")
and press Ctrl + Shift + Enter at the same time (Array Formula)
You can drag it also

Answer 2

Make a third column on each sheet with the formula

= A2 & " " & B2

Use that column for matching.

Answer 3

Using Tables and structured references it can be fairly simple. For each data table, Insert ► Tables ► Table

And this method also allows you to refer to the column headers by name.

The default names will be Table1 and Table2 -- the first table you create will be 1, and the second 2, but you can rename under Formula ► Name Manager

Then, for Table2 in C1, add a column Header (e.g. "In Table 1". A third column for the table should appear.

C2: =IF(SUMPRODUCT(([@NAME]=Table1[NAME])*([@DOB]=Table1[DOB])),"YES","NO")

The formula will automatically propagate down and give you your results.

Another option, which works assuming the NAME column is column 1, would be:

C2: =IF(VLOOKUP([@NAME],Table1,2,FALSE)=[@DOB],"YES","NO")

With structured references in tables, @NAME refers to the entry in the same line of the formula, under the column header "NAME"; Table1[NAME] refers to the entire column (except the header) in Table1. [NAME] by itself would refer to the entire column in the same table.

Another advantage of using Tables is that the table (and references) will automatically expand/contract as you add/delete adjacent rows or columns.