combine three if functions

[robmin1983]

Hi there this is my first post, i can normally work thing out by goin
through the forums and tweaking my stuff but im stuck. here is th
problem.

i have 3 formulas that work on their own
=IF(ISNUMBER(SEARCH("a",F23)),VLOOKUP(A23,Sheet2!$A$1:$A$1000:Sheet2!$K$1:$K$1000,9,FALSE),"")

=IF(ISNUMBER(SEARCH("b",F23)),VLOOKUP(A23,Sheet2!$A$1:$A$1000:Sheet2!$K$1:$K$1000,10,FALSE),"")

=IF(ISNUMBER(SEARCH("c",F23)),VLOOKUP(A23,Sheet2!$A$1:$A$1000:Sheet2!$K$1:$K$1000,11,FALSE),"")

what i think they say is that if cell f23 contains "a" look up data fro
cell a23 and insert correct cell from sheet 2.

now this works well if only i didnt need to combine them

what i need is this

if f23 contains a do the vlookup in column 9 if b use column 10 if c us
column 11 if 9,10&11 blank leave cell blank

what i came up with is

=IF(ISNUMBER(SEARCH("a",F27)),VLOOKUP(A27,Sheet2!$A$1:$A$1000:Sheet2!$K$1:$K$1000,9,FALSE),IF(ISNUMBER(SEARCH("b",F27)),VLOOKUP(A27,Sheet2!$A$1:$A$1000:Sheet2!$K$1:$K$1000,10,FALSE),IF(ISNUMBER(SEARCH("c",F27)),VLOOKUP(A27,Sheet2!$A$1:$A$1000:Sheet2!$K$1:$K$1000,11,FALSE),"")))

this works for a and b but not c and deos not leave blank brings up th
answer to b.


please can anyone hel

 

[joeu2004]

=IF(ISNUMBER(SEARCH("a",F27)),VLOOKUP(A27,Sheet2!$A$1:$A$1000:Sheet2!$K$1:$K$1000,9,FALSE),IF(ISNUMBER(SEARCH("b",F27)),VLOOKUP(A27,Sheet2!$A$1:$A$1000:Sheet2!$K$1:$K$1000,10,FALSE),IF(ISNUMBER(SEARCH("c",F27)),VLOOKUP(A27,Sheet2!$A$1:$A$1000:Sheet2!$K$1:$K$1000,11,FALSE),"")))

this works for a and b but not c and deos not leave blank brings up the
answer to b.

Off-hand, I do not see why that would not work as intended. I will keep
looking. Some thoughts....

Did you copy-and-paste from the Formula Bar into your posting?

If not, you might have unconsciously fixed the problem when you retyped it.

One comment.... The range Sheet2!$A$1:$A$1000:Sheet2!$K$1:$K$1000 can be
written more simply as Sheet2!$A$1:$K$1000. Simplifying formulas makes them
more readable, and it is easier to see mistakes.

Another comment.... Do you really need to do searches? Are the characters
"a", "b" and "c" in the same place in F27 all the time?

Even if they are not, I am tempted to suggest the following (untested):

=IF(COUNTIF(F27,"*a*")+COUNTIF(F27,"*b*")+COUNTIF(F27,"*c*")>0,
VLOOKUP(A27,Sheet2!$A$1:$K$1000,9+COUNTIF(F27,"*b*")+2*COUNTIF(F27,"*c*"),FALSE),"")

That assumes that the presence of "a", "b" and "c" in F27 is
mutually-exclusive. That is, only one of those characters (if any) will be
present in F27 at a time.

I say that I am "tempted" because even though that formula is shorter and
perhaps easier to read, it is less efficient. On the other hand, you might
not notice the performance difference. It's a judgment call.

Final comment.... What version of Excel are you using? Will your VLOOKUP
always succeed as long as "a", "b" or "c" is present in F27?

If not, the simpler formula above makes it easier to handle the VLOOKUP
error. But exactly how depends on whether you need Excel 2003
compatibility, or if we can assume Excel 2007 and later.

 

[joeu2004]

Off-hand, I do not see why that would not work as intended. I will keep
looking.

I confirmed that the formula works for "c" as well as "a" and "b" exactly as
you entered it in your posting.

Again, I suspect the problem (our inability to reproduce your error) is that
you retyped the formula instead of copy-and-pasting from the Formula Bar,
and you unconsciously correct the original error.

Potential remedy: simply copy-and-paste from your posting back into Excel,
as I did.

Will your VLOOKUP always succeed as long as "a", "b" or "c"
is present in F27?

If so, is Sheet2!A1:A1000 sorted in ascending order?

If so, it would be better to use VLOOKUP(...,TRUE) instead of
VLOOKUP(...,FALSE).

It is a difference between a max of 10 comparisons v. an average of 500 and
a max of 100 comparisons. The difference can be noticable if you are
copying this formula down a long column.

 

[robmin1983]

Thanks for some reason my excel did not like my original formula bu
your count if worked like a charm. just had to remove a space toward
the end after "*b*"

thanks agai