2

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.

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

enter image description here

=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

  • Yass, Thank you so much! I appreciate your answer. I implemented the formula in the spreadsheet but it only looks up until the first 6 rows with a yes or no value. After I change the $A$2:$A$5 array to $A$2:$A$12 I get a #VALUE!, I also tried to implement $A$2:$A:$A as well for $B I get the same error. Any thought on that ? – Gestef Mar 15 '17 at 2:31
  • you have to change the formula according to your Data if you have till row 12 and using 5 you will not get result write the reference you have – yass Mar 15 '17 at 7:27
  • =IF(SUMPRODUCT(--('Worksheet A'!$A$2:$A$12='Worksheet B'!A2)*(--'Worksheet A'!$B$2:$B$12='Worksheet B'!B2))=1,"Yes","No") if your Data is A2:A12 and B2:B12 and change worksheet A and worksheet B to your sheet do not use A:A and B:B – yass Mar 15 '17 at 7:36
0

Make a third column on each sheet with the formula

= A2 & " " & B2

Use that column for matching.

0

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.