Lookup Help

  • Thread starter aeast50 - ExcelForums.com
  • Start date
A

aeast50 - ExcelForums.com

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,(VLOOKUP(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
 
C

CLR

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
 
D

Dave Peterson

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.)
 
Top