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

Andrew,

The link below may be of some help.
http://www.techonthenet.com/excel/fo...2d_lookup2.htm


"aeast50 - ExcelForums.com" wrote
in message ...
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

you can do this by CONCATENATION..........

=VLOOKUP(A2&"-"&B2,NewConcatenatedTable,2,FALSE)

Then add a new row to the left of your existing table with the CONCATENATED
values to look up.........a.smith-a, a.smith-b, etc etc.........


Vaya con Dios,
Chuck, CABGx3


"aeast50 - ExcelForums.com" wrote
in message ...
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

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