if and vlookup functions
 

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,f alse),
if(f96="teamB"),vlookup(a96,TeamB!$a$4:$f$100,3,fa lse),
if(f96="teamC"),vlookup(a96,TeamC!$a$4:$f$100,3,fa lse)

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

if and vlookup functions
 

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

--
Don Guillett
SalesAid Software

"GD Raynor" <GD wrote in message
...
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,f alse),
if(f96="teamB"),vlookup(a96,TeamB!$a$4:$f$100,3,fa lse),
if(f96="teamC"),vlookup(a96,TeamC!$a$4:$f$100,3,fa lse)

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

if and vlookup functions
 

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,f alse),
if(f96="teamB"),vlookup(a96,TeamB!$a$4:$f$100,3,fa lse),
if(f96="teamC"),vlookup(a96,TeamC!$a$4:$f$100,3,fa lse),"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



GD Raynor wrote:
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,f alse),
if(f96="teamB"),vlookup(a96,TeamB!$a$4:$f$100,3,fa lse),
if(f96="teamC"),vlookup(a96,TeamC!$a$4:$f$100,3,fa lse)

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

if and vlookup functions
 

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


"GD Raynor" wrote:

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,f alse),
if(f96="teamB"),vlookup(a96,TeamB!$a$4:$f$100,3,fa lse),
if(f96="teamC"),vlookup(a96,TeamC!$a$4:$f$100,3,fa lse)

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

if and vlookup functions
 

Hi

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

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

--
Regards

Roger Govier


"GD Raynor" <GD wrote in message
...
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,f alse),
if(f96="teamB"),vlookup(a96,TeamB!$a$4:$f$100,3,fa lse),
if(f96="teamC"),vlookup(a96,TeamC!$a$4:$f$100,3,fa lse)

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