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

Organization Name	User Name	UserEmail	HPCertification
Blue Label	Joe Bloggs	joeB@BlueLabel.org
Red Wolf	Dan Smith	DanS@Redwold.org

<tbody>
</tbody>

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

Organization Name<strike></strike>	User Name<strike></strike>	UserEmail<strike></strike>	Certification<strike></strike>
Blue Label<strike></strike>	Joe Bloggs<strike></strike>	joeB@BlueLabel.org<strike></strike>	HPCert
Blue Label<strike></strike>	Joe Bloggs<strike></strike>	joeB@BlueLabel.org<strike></strike>	DellCert
Blue Label<strike></strike>	Joe Bloggs<strike></strike>	joeB@BlueLabel.org<strike></strike>	AppleCert
Red Wolf<strike></strike>	Dan Smith<strike></strike>	DanS@Redwold.org<strike></strike>	HPCert
Red Wolf<strike></strike>	Dan Smith<strike></strike>	DanS@Redwold.org<strike></strike>	DellCert
Red Wolf<strike></strike>	Dan Smith<strike></strike>	DanS@Redwold.org<strike></strike>	AppleCert
Red Wolf<strike></strike>	Dan Smith<strike></strike>	DanS@Redwold.org<strike></strike>	CiscoCert
Red Wolf<strike></strike>	Dan Smith<strike></strike>	DanS@Redwold.org<strike></strike>	AndroidCert

<tbody>
</tbody>

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.