With the name in A2 and the club in B2:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

"aeast50 - ExcelForums.com" wrote:

I have a table that list names,club and stats for sporting
competition.

My problem is the stats are recieved with the name as - A.Name

as there are multiple players with the same name i need to create a
lookup function that finds the name and the club, so i can then
reference the stats in the associated col. like

name club stats
a.smith a blah
a.smith b blah

IF(ISNA(VLOOKUP(TRIM(B3),Stats,2,FALSE)),0,(VLOOKU P(TRIM(B3),Stats,2,FALSE)))

I currently use this formula to see if the player played and then pull
the stat

Now i need to modify the formula to see if it's the player and the
club.

Any ideas - I have been trying to use AND and Match but can't get the
formula to look past the first instance of the name.

Thanks

Andrew

--

Dave Peterson