Vlook up question again I think or some look up thing

[andy009]

I have 2 cloumns of data and I need to find a name in a column and then report the name at the top of that column. A simple Example.
____A__________________B
1. Als' team________Jo's team
2. Fred 66____________ Julie 98
3. George 69__________ Jenny 99
4. Brad 80____________ Eddie 54






George * _____Here I need it to look up in the top section find the name (eg.George) and Repot either Al's Team or Jo's Team which ever the agent belongs to.

Don't worry about the scores I can remove them I just it need to search the name to find the team name.

Thanks for any help

Andreas

 

[Domenic]

Assuming that A1:B4 contains the table, and D2 contains the name of interest, such as George, try the following...

If each column within the table contains both the name and score...

Confirmed with CONTROL+SHIFT+ENTER:

=INDEX($A$1:$B$1,SMALL(IF(LEFT($A$2:$B$4,LEN(D2))=D2,COLUMN($A$2:$B$4)-COLUMN($A$2)+1),1))

If each column within the table contains the name only...

Confirmed with CONTROL+SHIFT+ENTER

=INDEX($A$1:$B$1,SMALL(IF($A$2:$B$4=D2,COLUMN($A$2:$B$4)-COLUMN($A$2)+1),1))

Hope this helps!

 

[andy009]

You Rock, thankyou!!!