if and vlookup functions

[Guest]

I need to do a function that will look for one of three criterion in a cell,
if condition a,b, or c is met, then I need to lookup a person's name and then
assign them to a team. So far I've tried the following formulae, but it turns
up NA or TRUE.

=if(f96="teamA"),vlookup(a96,TeamA!$a$4:$f$100,3,false),
if(f96="teamB"),vlookup(a96,TeamB!$a$4:$f$100,3,false),
if(f96="teamC"),vlookup(a96,TeamC!$a$4:$f$100,3,false)

Please advise as I'm quite new to this. Regards.

 

[Don Guillett]

try this idea
=IF(OR(D4="a",D4="b",D4="c"),VLOOKUP(D4,D1:E3,2),"NotThere")

 

[Pete_UK]

The general form for IF is:

=IF(condition, action_if_true, action_if_false)

and you can nest these together (up to 7 times) as you have done.
However, you still need one open and one closed bracket for each IF,
and you should try to cover all eventualities (i.e. what do you want if
F96 doesn't contain any of TeamA, TeamB or TeamC ? Try this:

=if(f96="teamA"),vlookup(a96,TeamA!$a$4:$f$100,3,false),
if(f96="teamB"),vlookup(a96,TeamB!$a$4:$f$100,3,false),
if(f96="teamC"),vlookup(a96,TeamC!$a$4:$f$100,3,false),"none")))

If A96 is not present in the table in the TeamA sheet when F96 = TeamA,
then the VLOOKUP formula will return an error #N/A - you can trap this
using a construction along the lines of:

=IF(ISNA(your_vlookup_formula),"not present",your_vlookup_formula)

but you can see that this will complicate your formula even more, as
you will have this for each of TeamA, TeamB and Team C.

Finally, as your VLOOKUP formulae are very similar, you could think
about replacing them with one formula using INDIRECT in the middle to
pick up the team name (and therefore sheet name) from F96.

Hope this helps.

Pete

 

[Guest]

=IF(OR(F96={"teamA","teamB","teamC"}),VLOOKUP(F96,A4:F100,3,0))

 

[Roger Govier]

Hi

I think I would be inclined to create 3 named ranges.
Insert>Name>Define>Name>TeamA >Refers to> TeamA!$A$4:$F$100
Repeat for TeaamB and TeamC

Then your formula simplifies to
=VLOOKUP(A96,INDIRECT(F96),3,0)