formula to find the first cell with data in it that is also listed in another range.

[tonywatsonhelp]

Hi everyone,
I'm very stuck please help if you can

I want a formula or formulas that can give me the first cell value in a range (P2:P150) this list does include blanks so ignore them.
but only if it it also included in range(T2:AA2) aginain which has blanks and this list can be in any order.

so fo example
range(T2:AA2) has "Tony",,"jef ",John","sue",,, "Bill"

range (P2:P150) has
Robin
Wills
ange
Sue
Mark
Tony,

[ want the formula to give me "Sue" as she is the first in range (P2:P150) that is also in range(T2:AA2)
please help if you can

 

[Eric W]

How about:

Book1
	P	Q			T	U	V	W	X	Y	Z	AA	AB	AC
1														Sue
2	Robin				Tony		jef	John	Sue			Bill
3	Wills
4
5	Ange
6
7	Sue
8	Mark
9
10	Tony
11
Sheet2

Cell Formulas
Range	Formula
AC1	AC1	=INDEX(P:P,AGGREGATE(15,6,ROW(P2:P150)/(COUNTIF(T2:AA2,P2:P150)>0),1))