check if value is in column after horizontal lookup

F1Fantje

New Member
Joined
Dec 8, 2020
Messages
1
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

excelhelp.png


Any help is appreciated.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1AndrewGaryPaulPhil
26167
37914
48752
51797
63641
7
8NamePaulGary
9Number93
10Exists?YesNo
Check
Cell Formulas
RangeFormula
B10:C10B10=IF(COUNTIF(INDEX($A2:$D6,0,MATCH(B8,$A1:$D1,0)),B9),"Yes","No")
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
586
Office Version
  1. 2016
Platform
  1. Windows
Formula in D10 =IF(ISNUMBER(VLOOKUP(B9,A2:D6,MATCH(B8,A1:D1,0),0)),"Yes","No")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
586
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1AndrewGaryPaulPhil
26167
37914
48752
51797
63641
7
8NamePaulGary
9Number93
10Exists?YesNo
11NoYes
Check
Cell Formulas
RangeFormula
B10:C10B10=IF(COUNTIF(INDEX($A2:$D6,0,MATCH(B8,$A1:$D1,0)),B9),"Yes","No")
B11:C11B11=IF(ISNUMBER(VLOOKUP(B9,$A2:$D6,MATCH(B8,$A1:$D1,0),0)),"Yes","No")
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
586
Office Version
  1. 2016
Platform
  1. Windows
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
ABCD
1AndrewGaryPaulPhil
26167
37914
48752
51797
63641
7
8NamePaulGary
9Number93
10Exists?YesNo
11NoYes
Check
Cell Formulas
RangeFormula
B10:C10B10=IF(COUNTIF(INDEX($A2:$D6,0,MATCH(B8,$A1:$D1,0)),B9),"Yes","No")
B11:C11B11=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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top