HLOOKUP adjacent cells

[Paul Naylor]

Hi, Hoping someone can help me.

I've got the following formula:

=HLOOKUP(A154,'P1'!G2:AY46,35,0)

Where A154 is a name from a list , which I want to HLOOKUP to pull the results from Column 35. The complication ( at least for me ) is that the row i'm looking up against on P1 sheet contains name which is merged over 3 cells , as next row down contains 3 seperate columns for each name and i want to extract the data from column 3

e.g. Row 2 : G2-I2 , J2-L2,M2-O2.....etc are merged cells with names in
Row 3 : G3 is Call 1, H2 is Call 2, I3 is Call3 ( for the 1st name )....J3 is call 1 , K3 is call 2 & L3 is call 3 ( for 2nd name) etc..... so I want to be able to Hlookup the 35th row of the 3rd column of the name i.e. Call 3.

Index function doesnt seem appropriate - need to find some way to search on multiple criteria i.e. row 2 then row 3? Hoping someone can help me out ? ( Using up to date version of excel 365)

Kind Regards,

Paul

 

[bebo021999]

may be:

=INDEX('P1'!$G35:$AY35,MATCH(A154,'P1'!$G$2:$AY$2,0)+2)

 

[Fluff]

Using up to date version of excel 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Paul Naylor]

Hlookup Issue .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O																																				AY	AZ	BA
1
2							John Smith			James Bond			Clark Gabel
3							P1 Call 1	P1 Call 2	P1 Call 3	P1 Call 1	P1 Call 2	P1 Call 3	P1 Call 1	P1 Call 2	P1 Call 3																																				P1 Call 3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
P1

 

[Paul Naylor]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Fluff, would normally do but i'm using 2 versions of excel ( works on network is excel 97 and on laptop is 365 ), so tend to put on any posts which version i need . Thanks for all your support

 

[Paul Naylor]

may be:

=INDEX('P1'!$G35:$AY35,MATCH(A154,'P1'!$G$2:$AY$2,0)+2)

Hi Bebo21999 thanks for quick response and it works a treat , just needed to change row no and add error handling , so complete one is : =IFERROR(INDEX('P1'!$G$46:$AY$46,MATCH(A153,'P1'!$G$2:$AY$2,0)+2),"-")