Check if multiple values exist in other columns

[Wishmaster89]

Hi

I want to check that both ID and Date from the right side match the value in the columns on the left. If both matches are true i want to vlookup(the easy bit!) to return programme name into column I.

I have tried using a match statement but can't get it to work.

Can any one help please?

if and match.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Date	programme				ID	Date	programme
2	123	01/11/2021	maths				222	01/12/2021	4		Yes
3	333	01/02/2022	english				123	01/11/2021			Yes
4	222	01/12/2021	history				333	01/02/2022			Yes
Sheet1

Cell Formulas
Range	Formula
I2	I2	=IF(MATCH(G2,A:A,0),(MATCH(H2,B:B,0)),"no match")
K2:K4	K2	=IF(MATCH(H2,B:B,0),"Yes","No")

 

[etaf]

countifs()
=COUNTIFS($A$2:$A$15,G2,$B$2:$B$15,H2)
and then use that in an IF
=IF( COUNTIFS($A$2:$A$15,G2,$B$2:$B$15,H2) > 0 , Vlookup() , "what to put if not a match")

Book7
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Date	programme				ID	Date	programme			Count
2	123	44501	maths				222	44531	4		Yes	1
3	333	44593	english				123	44501			Yes	1
4	222	44531	history				333	44593			Yes	1
5												0
6												0
7
Sheet1

Cell Formulas
Range	Formula
I2	I2	=IF(MATCH(G2,A:A,0),(MATCH(H2,B:B,0)),"no match")
K2:K4	K2	=IF(MATCH(H2,B:B,0),"Yes","No")
L2:L6	L2	=COUNTIFS($A$2:$A$15,G2,$B$2:$B$15,H2)

 

[etaf]

i thought i would add the full lookup included , using an index/match or if preferred can use a vlookup

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	Date	programme				ID	Date	programme			Count	Lookup	Programme
2	123	44501	maths				222	44531	4		Yes	1	history	history
3	333	44593	english				123	44501			Yes	1	maths	maths
4	222	44531	history				333	44593			Yes	1	english	english
5												0	0	no Match
6												0	0	no Match
7														no Match
8														no Match
Sheet1

Cell Formulas
Range	Formula
I2	I2	=IF(MATCH(G2,A:A,0),(MATCH(H2,B:B,0)),"no match")
L2:L6	L2	=COUNTIFS($A$2:$A$15,G2,$B$2:$B$15,H2)
M2:M6	M2	=INDEX($C$2:$C$15,MATCH(G2&H2,$A$2:$A$15&$B$2:$B$15,0))
K2:K4	K2	=IF(MATCH(H2,B:B,0),"Yes","No")
N2:N8	N2	=IF(COUNTIFS($A$2:$A$15,G2,$B$2:$B$15,H2)>0, INDEX($C$2:$C$15,MATCH(G2&H2,$A$2:$A$15&$B$2:$B$15,0)),"no Match")

 

[Wishmaster89]

@etaf Thank you so much, not sure why i didn't think of count if, doh!

 

[etaf]

you are welcome

 

[Wishmaster89]

What if there are multiple ID's to look up like this, the vlookup/match wouldn't know which programme to pull back?

if and match.xlsx
	A	B	C	D	E	F	G	H	I
1	ID	Date	programme				ID	Date	programme
2	123	01/11/2021	maths				222	01/12/2021	history
3	333	01/02/2022	english				123	01/11/2021	maths
4	222	01/12/2021	history				333	01/02/2022	english
5	222	01/02/2022	geography				123	25/01/2022
6	123	25/01/2022	IT				222	01/02/2022
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=IF(COUNTIFS($A$2:$A$15,G2,$B$2:$B$15,H2)>0,INDEX(C1:C4,MATCH(G2,A1:A4,0),0),"no")

 

[etaf]

no , it will only pull back the first one it finds
Off the top of my head not sure , but i know i have seen this solution a few times
Would you want both showing
OR first OR last
OR ?

 

[Wishmaster89]

no , it will only pull back the first one it finds
Off the top of my head not sure , but i know i have seen this solution a few times
Would you want both showing
OR first OR last
OR ?

The one where both id and date match.

 

[etaf]

NOTE - =IF(COUNTIFS($A$2:$A$15,G2,$B$2:$B$15,H2)>0,INDEX(C1:C4,MATCH(G2,A1:A4,0),0),"no") needs range changing and fixed anyway
=IF(COUNTIFS($A$2:$A$15,G2,$B$2:$B$15,H2)>0,INDEX($C$1:$C415,MATCH(G2,$A$1:$A415,0),0),"no")

BUT beside that - just for FYI

how about

=IF(COUNTIFS($A$2:$A$15,G2,$B$2:$B$15,H2)>0,INDEX($C$1:$C$15,MATCH(G2&H2,$A$1:$A$15&$B$1:$B$15,0),0),"no")
NOTE The range needs to cover the entire table - and needs $ to fix , so as you copy down it does not change

Book2
	A	B	C	D	E	F	G	H	I
1	ID	Date	programme				ID	Date	programme
2	123	11/1/21	maths				222	12/1/21	history
3	333	2/1/22	english				123	11/1/21	maths
4	222	12/1/21	history				333	2/1/22	english
5	222	2/1/22	geography				123	1/25/22	IT
6	123	1/25/22	IT				222	2/1/22	geography
7
Sheet1

Cell Formulas
Range	Formula
I2:I6	I2	=IF(COUNTIFS($A$2:$A$15,G2,$B$2:$B$15,H2)>0,INDEX($C$1:$C$15,MATCH(G2&H2,$A$1:$A$15&$B$1:$B$15,0),0),"no")

 

[Wishmaster89]

@etaf i will try in the morning thank you