How do I look up which column a value is in?

[mrpleasant]

I have a sheet which has team manager names in the top row as headings, with
a vertical list of their staff underneath in the same column

I want to conduct some sort of lookup function which will search for a staff
member then return their TM by looking in the top row of the particular
column they appear in.

Is this possible? I thought hlookup would be the answer but this only seems
happy to look in the top row.

 

[Mike H]

Hi,

I have assumed you team managers are in row 1 (A1 to E1) and that your table
is 10 rows deep and the name you are looking for is in f1. Try this array
formula

=INDEX(A1:E1,MIN(IF(A2:E10=F1,COLUMN(A2:E10)-MIN(COLUMN(A2:E10))+1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[mrpleasant]

Thanks, that's exactly what I was looking for. Not sure how it works, but it
does!

Just to complicate matters further, would there be a way of searching more
than one sheet with the same function?

 

[Mike H]

Hi,

Thanks for the feedback, I'm gald that worked for you.

Just to complicate matters further, would there be a way of searching more
than one sheet with the same function?

I'm not sure precisely what you mean but this now searches sheet 2 and
returns the header from the sheet the formula is on

=INDEX(A1:E1,MIN(IF(Sheet2!A2:E10=F1,COLUMN(Sheet2!A2:E10)-MIN(COLUMN(Sheet2!A2:E10))+1)))

Mike

 

[mrpleasant]

Hi again

Sorry, I should have made myself clearer.

I want to enter a value, and this would search multiple sheets and return
the value in Row 1 of that column on whichever sheet the name was on.

i.e.

Sheet 1:
TM 1 TM 2 TM 3 TM 4 TM 5
Staff 1 Staff 6 Staff 11 Staff 16 Staff 21
Staff 2 Staff 7 Staff 12 Staff 17 Staff 22
Staff 3 Staff 8 Staff 13 Staff 18 Staff 23
Staff 4 Staff 9 Staff 14 Staff 19 Staff 24
Staff 5 Staff 10 Staff 15 Staff 20 Staff 25

Sheet 2:
TM 6 TM 7 TM 8 TM 9 TM 10
Staff 26 Staff 31 Staff 36 Staff 41 Staff 46
Staff 27 Staff 32 Staff 37 Staff 42 Staff 47
Staff 28 Staff 33 Staff 38 Staff 43 Staff 48
Staff 29 Staff 34 Staff 39 Staff 44 Staff 49
Staff 30 Staff 35 Staff 40 Staff 45 Staff 50

So 'Staff 30' would return 'TM 6'

Hope this makes sense!

 

[Ashish Mathur]

Hi,

Let's say that your data is arranged like this in range D8:G13 (including
the headings). Enter 1,2,3,4 in D7:G7.

Manager 1 Manager 2 Manager 3 Manager 4
A F T N
S G Y M
D H U Q
J I
OIn cell D14, enter A. In cell E14, you can use the following formula
=CHOOSE(SUMPRODUCT(($D$9:$G$13=D14)*(D7:G7)),D8,E8,F8,G8)

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Hi,

I forgot to add that in the choose function, you can only specify upto 29
values I.e. only upto 29 managers

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mike H]

Hi,

This now works for sheet 1 and sheet 2 and if the lookup isn't found an
error message is returned

=IF(COUNTIF(A2:E10,F1)>0,INDEX(A11,MIN(IF(A2:E10=F1,COLUMN(A2:E10)-MIN(COLUMN(A2:E10))+1))),IF(COUNTIF(Sheet2!A2:E10,F1)=0,"Lookup
not
Found",INDEX(Sheet2!A11,MIN(IF(Sheet2!A2:E10=F1,COLUMN(Sheet2!A2:E10)-MIN(COLUMN(Sheet2!A2:E10))+1)))))

It's becoming a bit unweildy so to progress to more sheets then I'd resort
to VB

Once again array entered.

Mike