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" wrote:

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" wrote:

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.