Compare Columns in sheets and report values

[Shinho62]

I am hoping someone can guide me in the right Direction, I have got the basics of Vlookup, but want to take it a stage further

I have two sheets with identical entries in columns a - c

A= Organization Name
B = User Name
C = User Email

[TABLE="width: 500"]
<tbody>[TR]
[TD]Organization Name[/TD]
[TD]User Name[/TD]
[TD]UserEmail[/TD]
[TD]HPCertification[/TD]
[/TR]
[TR]
[TD]Blue Label[/TD]
[TD]Joe Bloggs[/TD]
[TD]joeB@BlueLabel.org[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red Wolf[/TD]
[TD]Dan Smith[/TD]
[TD]DanS@Redwold.org[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


However the 2nd sheet has a column D with Certificate details, a standard Vlookup would work except sheet two has multiple entries for columns B&C
I want it to check the names for a match then find a specific value in D lets say "HPCert" and then put a complete to sheet 1 column D for HPCert

[TABLE="width: 500"]
<tbody>[TR]
[TD]Organization Name<strike></strike>[/TD]
[TD]User Name<strike></strike>[/TD]
[TD]UserEmail<strike></strike>[/TD]
[TD]Certification<strike></strike>[/TD]
[/TR]
[TR]
[TD]Blue Label<strike></strike>[/TD]
[TD]Joe Bloggs<strike></strike>[/TD]
[TD]joeB@BlueLabel.org<strike></strike>[/TD]
[TD]HPCert[/TD]
[/TR]
[TR]
[TD]Blue Label<strike></strike>[/TD]
[TD]Joe Bloggs<strike></strike>[/TD]
[TD]joeB@BlueLabel.org<strike></strike>[/TD]
[TD]DellCert[/TD]
[/TR]
[TR]
[TD]Blue Label<strike></strike>[/TD]
[TD]Joe Bloggs<strike></strike>[/TD]
[TD]joeB@BlueLabel.org<strike></strike>[/TD]
[TD]AppleCert[/TD]
[/TR]
[TR]
[TD]Red Wolf<strike></strike>[/TD]
[TD]Dan Smith<strike></strike>[/TD]
[TD]DanS@Redwold.org<strike></strike>[/TD]
[TD]HPCert[/TD]
[/TR]
[TR]
[TD]Red Wolf<strike></strike>[/TD]
[TD]Dan Smith<strike></strike>[/TD]
[TD]DanS@Redwold.org<strike></strike>[/TD]
[TD]DellCert[/TD]
[/TR]
[TR]
[TD]Red Wolf<strike></strike>[/TD]
[TD]Dan Smith<strike></strike>[/TD]
[TD]DanS@Redwold.org<strike></strike>[/TD]
[TD]AppleCert[/TD]
[/TR]
[TR]
[TD]Red Wolf<strike></strike>[/TD]
[TD]Dan Smith<strike></strike>[/TD]
[TD]DanS@Redwold.org<strike></strike>[/TD]
[TD]CiscoCert[/TD]
[/TR]
[TR]
[TD]Red Wolf<strike></strike>[/TD]
[TD]Dan Smith<strike></strike>[/TD]
[TD]DanS@Redwold.org<strike></strike>[/TD]
[TD]AndroidCert[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone advise, driving me mad

 

[jtakw]

Hi,

Does this help:

Book1
	A	B	C	D
1	Organization Name	User Name	UserEmail	HPCertification
2	Blue Label	Joe Bloggs	joeB@BlueLabel.org	Complete
3	Red Wolf	Dan Smith	DanS@Redwold.org	Complete
4	Brown Bear	John Doe	JohnD@Bear.org	Not Complete
5
6	Organization Name	User Name	UserEmail	Certification
7	Blue Label	Joe Bloggs	joeB@BlueLabel.org	HPCert
8	Blue Label	Joe Bloggs	joeB@BlueLabel.org	DellCert
9	Blue Label	Joe Bloggs	joeB@BlueLabel.org	AppleCert
10	Red Wolf	Dan Smith	DanS@Redwold.org	HPCert
11	Red Wolf	Dan Smith	DanS@Redwold.org	DellCert
12	Red Wolf	Dan Smith	DanS@Redwold.org	AppleCert
13	Red Wolf	Dan Smith	DanS@Redwold.org	CiscoCert
14	Red Wolf	Dan Smith	DanS@Redwold.org	AndroidCert
15	Brown Bear	John Doe	JohnD@Bear.org	CiscoCert
16	Brown Bear	John Doe	JohnD@Bear.org	DellCert
Sheet200

Cell Formulas
Range	Formula
D2	=IF(SUMPRODUCT((A$7:A$16=A2)*(B$7:B$16=B2)*(C$7:C$16=C2)*(D$7:D$16="HPCert")),"Complete","Not Complete")

Change cell references/range and add sheet name to formula as needed.

Formula copied down.

 

[Shinho62]

jtakw

that looks good, I will give it a go and come back you

 

[Shinho62]

jtakw

It works great, thank you

 

[jtakw]

You're welcome, thanks for the feedback.

If you might change what you want to lookup (i.e. DellCert instead of HPCert or whatever else), instead of "hard-coding" the HPCert in the formula, use a cell to house the lookup criteria, then use a cell reference in the formula.