IF Then Statement

[LewR]

I have a question reguarding a data validation formula:

Currenty I have a formula in cell b14
=INDIRECT(VLOOKUP(A14,TRCLOOKUP,2,0)).

What I want to add is an IF statement which will display a differen
list (FOTRC) if the value in cell A14 is not in the "TRCLOOKUP" name
range.

Any sugestions

 

[Biff]

Hi!

Try this:

=INDIRECT(VLOOKUP(A14,INDIRECT(IF(COUNTIF(INDEX(TRCLOOKUP,,1),A14),"TRCLOOKUP","FOTRC")),2,0))

Biff

 

[John James]

Not sure I understand your question.

If TRCLOOKUP and FOTRC are two alternate names ranges of simila
structure and for similar (alternate) purposes, then try:

Formula in cell b14
=if(iserror(VLOOKUP(A14,TRCLOOKUP,2,0)),INDIRECT(VLOOKUP(A14,FOTRC,2,0)),INDIRECT(VLOOKUP(A14,TRCLOOKUP,2,0)))

Good luck,

P.S. If my assumptions are correct, you might need to consider
similar if(iserror(... condition for the FOTRC vlookup.

Tip:
Putting the vlookups for TRCLOOKUP and FOTRC in separate cells (e.g
b14 and c14 with the final formula instead in d14) will simplify th
formulae and improve calculation speed

 

[Biff]

I remember answering a post from this OP a couple of weeks ago?

The lookup tables contain the names of named ranges to be used as the source
for a drop down list(s).

Biff

 

[LewR]

Biff is correct, he did help me before.

I tried his response but it disd not do what I need it to do. Let m
restate the problem to clarify (or muddy) the air.

In cell A14 I have a Data Validation list that is controled by th
statement:=JobDescription. Job Description is a named range with
blank row included in the list so that the user may enter a value i
the values that are named in the list are not what the user needs.
Cell B14 is another Data Validation list with the following formula:
=INDIRECT(VLOOKUP(A14,TRCLookup,2,0)).

What I want to add to B14 is a way to have the named Range "FOTRC" t
be used instead of "TRCLookup" if the blank row is chosen in cell a14.

Hope this makes sense

 

[Biff]

Hi!

What I want to add to B14 is a way to have the named Range "FOTRC" to
be used instead of "TRCLookup" if the blank row is chosen in cell a14.

Ok, now it's getting confusing!

If A14 is blank then use FOTRC in the lookup?

=INDIRECT(VLOOKUP(A14,FOTRC,2,0))

Well, A14 is blank and unless you have a specially crafted "blank" selection
in the lookup table then there's nothing to lookup!

Need more info.

Biff

 

[LewR]

OK, I have solved part of the problem. in cell B14 I now have the Data
Validation Statement:
=IF(A14="",FOList,(INDIRECT(VLOOKUP(A14,TRCLookup,2,0))))

"FOLookup" is a replacement for "FOTRC".

This works as long as A14 is blank. How can I rewrite the formula in
B14 to produce the same results if the value in A14 is not listed in
the named range "TRCLookup"? The values in A14 will be text entries,
not numbers.

 

[Biff]

Try this:

=IF(A14="",FOList,IF(ISNA(VLOOKUP(A14,TRCLookup,2,0)),FOList,INDIRECT(VLOOKUP(A14,TRCLookup,2,0))))

Biff

 

[LewR]

Thanks for the help, works like a dream

 

[Harlan Grove]

Biff wrote...

Try this:

=IF(A14="",FOList,IF(ISNA(VLOOKUP(A14,TRCLookup,2,0)),
FOList,INDIRECT(VLOOKUP(A14,TRCLookup,2,0))))

....

Bit redundant. How about

=IF(OR(A14="",ISNA(VLOOKUP(A14,TRCLookup,2,0))),FOList,
INDIRECT(VLOOKUP(A14,TRCLookup,2,0)))

 

[Biff]

You're welcome. Glad we got it straightened out!


Biff