Find if value in B1 (sheet2) is in Column (on sheet 1) with a header name in A1 (Sheet2)

[surkdidat]

Hopefully this makes sense!

On Sheet 1 I have my master data sheet, with Column Headers in Row 1 and the values relating to that in the cells below.

On Sheet 2, in column A I have the name of a column header, and in Column B (on same row) I have a value.

I would like it to come back "Yes", or "No", depending if the value is in that column


Sheet1

ANIMAL	CITY
Horse	Aberdeen
Cat	Belfast
Dog	Cardiff

Sheet2

ANIMAL	Cat	YES
ANIMAL	Elephant	No
CITY	Aberdeen	YES
CITY	Darlington	NO

 

[Peter_SSs]

Try something like this. You may need to adjust the ranges to suit your actual data size and layout.

surkdidat.xlsm
	A	B
1	ANIMAL	CITY
2	Horse	Aberdeen
3	Cat	Belfast
4	Dog	Cardiff
Sheet1

surkdidat.xlsm
	A	B	C
1	Column	Value	Exists?
2	ANIMAL	Cat	YES
3	ANIMAL	Elephant	NO
4	CITY	Aberdeen	YES
5	CITY	Darlington	NO
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=IF(ISNUMBER(MATCH(B2,INDEX(Sheet1!A$1:E$100,0,MATCH(A2,Sheet1!A$1:E$1,0)),0)),"YES","NO")

 

[surkdidat]

Try something like this. You may need to adjust the ranges to suit your actual data size and layout.

surkdidat.xlsm
	A	B
1	ANIMAL	CITY
2	Horse	Aberdeen
3	Cat	Belfast
4	Dog	Cardiff
Sheet1

surkdidat.xlsm
	A	B	C
1	Column	Value	Exists?
2	ANIMAL	Cat	YES
3	ANIMAL	Elephant	NO
4	CITY	Aberdeen	YES
5	CITY	Darlington	NO
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	=IF(ISNUMBER(MATCH(B2,INDEX(Sheet1!A$1:E$100,0,MATCH(A2,Sheet1!A$1:E$1,0)),0)),"YES","NO")

Superb! Thank yoU!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.