check if value is in column after horizontal lookup

[F1Fantje]

Hi all,

I can't get the formula to check if a given number is in the column under a given name. If the number is in the column under the name, then it should give something back like True or a symbol like X. If it's not in, the cell should stay empty.
I'm almost certain I need a horizontal lookup for the name, but I just can't find a way to then have a search in the column under the right name to check if the number is in it.

In cell D10 in the example below, I want to know if Andrew has the number 3 in the column under him.

Any help is appreciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this does what you want.

20 12 09.xlsm
	A	B	C	D
1	Andrew	Gary	Paul	Phil
2	6	1	6	7
3	7	9	1	4
4	8	7	5	2
5	1	7	9	7
6	3	6	4	1
7
8	Name	Paul	Gary
9	Number	9	3
10	Exists?	Yes	No
Check

Cell Formulas
Range	Formula
B10:C10	B10	=IF(COUNTIF(INDEX($A2:$D6,0,MATCH(B8,$A1:$D1,0)),B9),"Yes","No")

 

[Zot]

Formula in D10 =IF(ISNUMBER(VLOOKUP(B9,A2:D6,MATCH(B8,A1:D1,0),0)),"Yes","No")

 

[Peter_SSs]

Formula in D10 =IF(ISNUMBER(VLOOKUP(B9,A2:D6,MATCH(B8,A1:D1,0),0)),"Yes","No")

Surely that only tells us whether the B9 value exists in column A or not?

 

[Zot]

Surely that only tells us whether the B9 value exists in column A or not?

Worked as I tested it based on example sheet provided

 

[Peter_SSs]

Worked as I tested it based on example sheet provided

That is because the particular example was for Andrew who happens to be in column A. The OP asked for ..

check if a given number is in the column under a given name.

.. so testing your formula with my sample data gives No for Paul and Yes for Gary, both of which are incorrect (at least by my reading of the requirement)

20 12 09.xlsm
	A	B	C	D
1	Andrew	Gary	Paul	Phil
2	6	1	6	7
3	7	9	1	4
4	8	7	5	2
5	1	7	9	7
6	3	6	4	1
7
8	Name	Paul	Gary
9	Number	9	3
10	Exists?	Yes	No
11		No	Yes
Check

Cell Formulas
Range	Formula
B10:C10	B10	=IF(COUNTIF(INDEX($A2:$D6,0,MATCH(B8,$A1:$D1,0)),B9),"Yes","No")
B11:C11	B11	=IF(ISNUMBER(VLOOKUP(B9,$A2:$D6,MATCH(B8,$A1:$D1,0),0)),"Yes","No")

 

[Zot]

That is because the particular example was for Andrew who happens to be in column A. The OP asked for ..

.. so testing your formula with my sample data gives No for Paul and Yes for Gary, both of which are incorrect (at least by my reading of the requirement)

20 12 09.xlsm
	A	B	C	D
1	Andrew	Gary	Paul	Phil
2	6	1	6	7
3	7	9	1	4
4	8	7	5	2
5	1	7	9	7
6	3	6	4	1
7
8	Name	Paul	Gary
9	Number	9	3
10	Exists?	Yes	No
11		No	Yes
Check

Cell Formulas
Range	Formula
B10:C10	B10	=IF(COUNTIF(INDEX($A2:$D6,0,MATCH(B8,$A1:$D1,0)),B9),"Yes","No")
B11:C11	B11	=IF(ISNUMBER(VLOOKUP(B9,$A2:$D6,MATCH(B8,$A1:$D1,0),0)),"Yes","No")

I have deleted my workbook, but looking back into my formula obviously it could not have work with VLOOKUP looking on the very left column ?
I remembered testing with other name as well with various number and it was okay coincidentally. Not sure if I copied wrong one though

Thanks for correcting