I have a data sheet that contains a list of footballers (soccer) names
(j4:j35). Each time they score a goal i enter a 1 by the side of there
name, then a 2 when they get a second etc. In a second data sheet i
use data validation to allow people to choose anyone of these players
eg ab4= the list of players. When a player scores i want their number
of goals to appear in this second sheet eg in ac4, but obviously this
depends on which player has been chosen from the list in ab4. I have
tried using if statements within if statements but you can only have a
maximum of 7 'ifs' eg
=if(ab4="john",$j$4,if(ab4="dan",$j$5,if(ab4="ron" ,$j$6,0))) can any
help so i can do this for all 35 players in my list please?


--
Blade2304
------------------------------------------------------------------------
Blade2304's Profile: http://www.excelforum.com/member.php...o&userid=33492
View this thread: http://www.excelforum.com/showthread...hreadid=542369

Hi,

If you have number of goals in k4:k35 then maybe you can use a
VLookUp(). Something like this in AC4

=VLOOKUP(AB4,J4:K35,2)

Regards,
Bondi

I tried this and it didnt work since the table array has to be sorted in
ascending order for it to work and since the values in the column of
values being returned changes it will not do what i want it to. Thanks
for trying. Any other ideas?


--
Blade2304
------------------------------------------------------------------------
Blade2304's Profile: http://www.excelforum.com/member.php...o&userid=33492
View this thread: http://www.excelforum.com/showthread...hreadid=542369

Hi,

Ok.Try this then

=VLOOKUP(AB4,J4:K35,2,FALSE)

If you set the last argument to FALSE then the data does not need to be
sorted.
Regards,
Bondi