Home |
Search |
Today's Posts |
#1
|
|||
|
|||
combine three if functions
Hi there this is my first post, i can normally work thing out by going through the forums and tweaking my stuff but im stuck. here is the 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 from 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 use 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(ISNUMB ER(SEARCH("b",F27)),VLOOKUP(A27,Sheet2!$A$1:$A$100 0: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. please can anyone help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combine three if functions
"robmin1983" wrote:
=IF(ISNUMBER(SEARCH("a",F27)),VLOOKUP(A27,Sheet2!$ A$1:$A$1000:Sheet2!$K$1:$K$1000,9,FALSE),IF(ISNUMB ER(SEARCH("b",F27)),VLOOKUP(A27,Sheet2!$A$1:$A$100 0: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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combine three if functions
PS.... I wrote:
"robmin1983" wrote: =IF(ISNUMBER(SEARCH("a",F27)),VLOOKUP(A27,Sheet2!$ A$1:$A$1000:Sheet2!$K$1:$K$1000,9,FALSE),IF(ISNUMB ER(SEARCH("b",F27)),VLOOKUP(A27,Sheet2!$A$1:$A$100 0: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. 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. I wrote: 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. |
#4
|
|||
|
|||
Thanks for some reason my excel did not like my original formula but your count if worked like a charm. just had to remove a space towards the end after "*b*"
thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine ISNA and IF functions | Excel Worksheet Functions | |||
Combine text functions | Excel Discussion (Misc queries) | |||
How to combine, IF, AND, OR Functions | Excel Worksheet Functions | |||
Possible to combine VLOOKUP and IF(AND) functions? | Excel Worksheet Functions | |||
Combine functions? | Excel Worksheet Functions |