Lookup Value in an Array

[PaulGotts]

Hello All, need assistance here... a real brain buster for me.

Using the array below, how would I create a formula that finds and returns a cell or column reference?

i.e. via a formula, I want to find the column which contains "Andy" and "124" (there may be duplicate values in the array).

Andy	Brian	Andy	Andy	Brian	Andy	Brian	Brian	Andy	Brian	Brian	Andy	Brian	N	O	P
117	112	102	8	3	25	417	419	11	35		47	20	429	420	440
		119	14	7	31		124		63		76	21
			18	9					121		406	44
			21	12								53
			28	26								56
			29	31								58
			29	33								110
			36	34								403
			38	42								405
			38	43								412
			50	49								414
			52	50								416
			60	57								434
			61	59								435
			64	62								437
			77	69								446
			80	70
			82	71
			86	81
			87	82
			89	100
			91	103
			96	132
			97	135
			98	406
			99	413
			103	415
			111	416
			111	419
			118	421
			124	429
			127	431
			401	432
			402	437
			422	441

<TBODY>
</TBODY>

 

[Firefly2012]

Hi

Perhaps this:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Andy	Brian	Andy	Andy	Brian	Andy	Brian	Brian	Andy	Brian	Brian	Andy	Brian	N	O	P		Andy
2	117	112	102	8	3	25	417	419	11	35		47	20	429	420	440		124
3			119	14	7	31		124		63		76	21					4
4				18	9					121		406	44
5				21	12								53
6				28	26								56
7				29	31								58
8				29	33								110
9				36	34								403
10				38	42								405
11				38	43								412
12				50	49								414
13				52	50								416
14				60	57								434
15				61	59								435
16				64	62								437
17				77	69								446
18				80	70
19				82	71
20				86	81
21				87	82
22				89	100
23				91	103
24				96	132
25				97	135
26				98	406
27				99	413
28				103	415
29				111	416
30				111	419
31				118	421
32				124	429
Sheet1

Cell Formulas
Range	Formula
R3	{=MIN(IF($A$1:$P$1=R1,IF(COUNTIF(OFFSET($A$2:$A$36,0,COLUMN($A$1:$P$1)-COLUMN($A$1)),R2)>0,COLUMN($A$1:$P$1)-COLUMN($A$1)+1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[PaulGotts]

Once adjusted for my application this has worked wonderfully. Thank you very much.

 

[Firefly2012]

Paul, you're welcome