|
If statement help
Hi All,
I need to write an if statement that says in non formula terms, if CELL A1 = 123, do vlookup etc or if CELL A1 = 456 do vlookup or of CELL A1 = 789 do vlookup I know I can do a repeated nested if, but when the list of what cell A1 could be is very long, my nested IF statement will become unmanageable. What i want to do is a vlookup based upon the value in cell A1. So, instead of nesting it, is there a way to say look at the value of cell A1 and =if it is in a seperate list, do the vlookup, if not do something else. So, if cell A1 (is in a list of ABC,DEF,GHI, JKL and so on) do the vlookup, if not do something else. Its gettign the IF to look to see if cell A1 is in a list that I cant do. I hope ive managed to explain myself. Thanks Rob |
If statement help
One solution (if your list is stored in a string):
=IF(ISERROR(SEARCH(A1,"123,456")),"do something else","do Vlookup") Replace "do something else","do Vlookup" strings with the appropriate functions! Regards, Stefi Gaffnr ezt *rta: Hi All, I need to write an if statement that says in non formula terms, if CELL A1 = 123, do vlookup etc or if CELL A1 = 456 do vlookup or of CELL A1 = 789 do vlookup I know I can do a repeated nested if, but when the list of what cell A1 could be is very long, my nested IF statement will become unmanageable. What i want to do is a vlookup based upon the value in cell A1. So, instead of nesting it, is there a way to say look at the value of cell A1 and =if it is in a seperate list, do the vlookup, if not do something else. So, if cell A1 (is in a list of ABC,DEF,GHI, JKL and so on) do the vlookup, if not do something else. Its gettign the IF to look to see if cell A1 is in a list that I cant do. I hope ive managed to explain myself. Thanks Rob |
If statement help
Thanks Stefi but it didnt work :-(
The search function is not doing its part I think. My list of checks are on sheet 2, in cells A1 - A10 say. So, I want my if statement on sheet 2 to do: =if(C1 is in the list on Sheet!A1:A10,"then do vlookup","do something else") -- Rob Gaffney "Stefi" wrote: One solution (if your list is stored in a string): =IF(ISERROR(SEARCH(A1,"123,456")),"do something else","do Vlookup") Replace "do something else","do Vlookup" strings with the appropriate functions! Regards, Stefi Gaffnr ezt *rta: Hi All, I need to write an if statement that says in non formula terms, if CELL A1 = 123, do vlookup etc or if CELL A1 = 456 do vlookup or of CELL A1 = 789 do vlookup I know I can do a repeated nested if, but when the list of what cell A1 could be is very long, my nested IF statement will become unmanageable. What i want to do is a vlookup based upon the value in cell A1. So, instead of nesting it, is there a way to say look at the value of cell A1 and =if it is in a seperate list, do the vlookup, if not do something else. So, if cell A1 (is in a list of ABC,DEF,GHI, JKL and so on) do the vlookup, if not do something else. Its gettign the IF to look to see if cell A1 is in a list that I cant do. I hope ive managed to explain myself. Thanks Rob |
If statement help
Hi
Create a named range for your list. InsertNameDefine Name myList Refers to =Sheet2!$A$1:$A$10 then =IF(A1="","",IF(ISNUMBER(SEARCH(A1,myList)),"do vlookup","do something else")) -- Regards Roger Govier "Gaffnr" wrote in message ... Thanks Stefi but it didnt work :-( The search function is not doing its part I think. My list of checks are on sheet 2, in cells A1 - A10 say. So, I want my if statement on sheet 2 to do: =if(C1 is in the list on Sheet!A1:A10,"then do vlookup","do something else") -- Rob Gaffney "Stefi" wrote: One solution (if your list is stored in a string): =IF(ISERROR(SEARCH(A1,"123,456")),"do something else","do Vlookup") Replace "do something else","do Vlookup" strings with the appropriate functions! Regards, Stefi "Gaffnr" ezt rta: Hi All, I need to write an if statement that says in non formula terms, if CELL A1 = 123, do vlookup etc or if CELL A1 = 456 do vlookup or of CELL A1 = 789 do vlookup I know I can do a repeated nested if, but when the list of what cell A1 could be is very long, my nested IF statement will become unmanageable. What i want to do is a vlookup based upon the value in cell A1. So, instead of nesting it, is there a way to say look at the value of cell A1 and =if it is in a seperate list, do the vlookup, if not do something else. So, if cell A1 (is in a list of ABC,DEF,GHI, JKL and so on) do the vlookup, if not do something else. Its gettign the IF to look to see if cell A1 is in a list that I cant do. I hope ive managed to explain myself. Thanks Rob |
If statement help
On Thu, 27 Sep 2007 03:56:00 -0700, Gaffnr
wrote: Hi All, I need to write an if statement that says in non formula terms, if CELL A1 = 123, do vlookup etc or if CELL A1 = 456 do vlookup or of CELL A1 = 789 do vlookup I know I can do a repeated nested if, but when the list of what cell A1 could be is very long, my nested IF statement will become unmanageable. What i want to do is a vlookup based upon the value in cell A1. So, instead of nesting it, is there a way to say look at the value of cell A1 and =if it is in a seperate list, do the vlookup, if not do something else. So, if cell A1 (is in a list of ABC,DEF,GHI, JKL and so on) do the vlookup, if not do something else. Its gettign the IF to look to see if cell A1 is in a list that I cant do. I hope ive managed to explain myself. Thanks Rob =if(countif(A1_lookup_list_range,A1)=0,"do something else", vlookup(A1,table_array,col_index_num,[range_lookup])) range_lookup is optional depending on whether your table_array is sorted by lookup value. --ron |
If statement help
Hi Roger,
I've tried this but it fails.... Ive tried to understand your formula but its a bit alien to me... InsertNameDefine Name myList Refers to =Sheet2!$A$1:$A$10 Done this and called it CompaniesList so following your formula, ive changed it to read as per my ssheet. =IF(C1="","",IF(ISNUMBER(SEARCH(C1,CompaniesList)) ,"do vlookup","do something else")) As I read the above, its saying, if C1 = "", put "". Thats cool Next part is where I struggle.... I read it as Then if not blank, = if C1 is a number, do vlookup, if not do something else? Ive tried the ISNUMBER on its own and it returns false Ive tried search and it returns an error. Im at my wits end with this........ aaaaaaaaaaaarrrrggggggggg Tks Rob As i read this, its sayi -- Rob Gaffney "Roger Govier" wrote: Hi Create a named range for your list. InsertNameDefine Name myList Refers to =Sheet2!$A$1:$A$10 then =IF(A1="","",IF(ISNUMBER(SEARCH(A1,myList)),"do vlookup","do something else")) -- Regards Roger Govier "Gaffnr" wrote in message ... Thanks Stefi but it didnt work :-( The search function is not doing its part I think. My list of checks are on sheet 2, in cells A1 - A10 say. So, I want my if statement on sheet 2 to do: =if(C1 is in the list on Sheet!A1:A10,"then do vlookup","do something else") -- Rob Gaffney "Stefi" wrote: One solution (if your list is stored in a string): =IF(ISERROR(SEARCH(A1,"123,456")),"do something else","do Vlookup") Replace "do something else","do Vlookup" strings with the appropriate functions! Regards, Stefi "Gaffnr" ezt *rta: Hi All, I need to write an if statement that says in non formula terms, if CELL A1 = 123, do vlookup etc or if CELL A1 = 456 do vlookup or of CELL A1 = 789 do vlookup I know I can do a repeated nested if, but when the list of what cell A1 could be is very long, my nested IF statement will become unmanageable. What i want to do is a vlookup based upon the value in cell A1. So, instead of nesting it, is there a way to say look at the value of cell A1 and =if it is in a seperate list, do the vlookup, if not do something else. So, if cell A1 (is in a list of ABC,DEF,GHI, JKL and so on) do the vlookup, if not do something else. Its gettign the IF to look to see if cell A1 is in a list that I cant do. I hope ive managed to explain myself. Thanks Rob |
If statement help
Hi Ron
Thanks for this but it doesnt work either. looks like ive found something that Excel cannot do. rob -- Rob Gaffney "Ron Rosenfeld" wrote: On Thu, 27 Sep 2007 03:56:00 -0700, Gaffnr wrote: Hi All, I need to write an if statement that says in non formula terms, if CELL A1 = 123, do vlookup etc or if CELL A1 = 456 do vlookup or of CELL A1 = 789 do vlookup I know I can do a repeated nested if, but when the list of what cell A1 could be is very long, my nested IF statement will become unmanageable. What i want to do is a vlookup based upon the value in cell A1. So, instead of nesting it, is there a way to say look at the value of cell A1 and =if it is in a seperate list, do the vlookup, if not do something else. So, if cell A1 (is in a list of ABC,DEF,GHI, JKL and so on) do the vlookup, if not do something else. Its gettign the IF to look to see if cell A1 is in a list that I cant do. I hope ive managed to explain myself. Thanks Rob =if(countif(A1_lookup_list_range,A1)=0,"do something else", vlookup(A1,table_array,col_index_num,[range_lookup])) range_lookup is optional depending on whether your table_array is sorted by lookup value. --ron |
If statement help
On Thu, 27 Sep 2007 06:32:02 -0700, Gaffnr
wrote: Hi Ron Thanks for this but it doesnt work either. looks like ive found something that Excel cannot do. rob -- Rob Gaffney I doubt that very much. Either you have not explained your requirements clearly, or you are not implementing properly the solutions that have been offered. You will have a better chance if you provide specific data. But with what you have provided, we can only guess at what might work. --ron |
| All times are GMT +1. The time now is 12:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com