Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
(IF FUNTION )HOW TO SHORT THIS FORMULA??
I MUST BE VERY DUMB AT THIS... PLEASE HELP..
here is my if fuction. =IF(C130=A123,C123,"")&IF(C130=A122,C122,"")&IF(C1 30=A121,C121,"")&IF(C130=A120,C120,"")&IF(C130=A11 9,C119,"")&IF(C130=A118,C118,"")&IF(C130=A117,C117 ,"")&IF(C130=A116,C116,"")&IF(C130=A115,C115,"")&I F(C130=A114,C114,"")&IF(C130=A113,C113,"")&IF(C130 =A112,C112,"")&IF(C130=A111,C111,"")&IF(C130=A110, C110,"")&IF(C130=A109,C109,"")&IF(C130=A108,C108," ")&IF(C130=A107,C107,"")&IF(C130=A106,C106,"")&IF( C130=A105,C105,"")&IF(C130=A104,C104,"").... and so on... I got error saying my formula is too long, how to make this short?? thanks paul |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
(IF FUNTION )HOW TO SHORT THIS FORMULA??
One way:
=INDEX(C104:C123,MATCH(C130,A104:A123,0)) If there's no match then the formula will retun #N/A. If you want a blank returned instead: =IF(COUNTIF(A104:A123,C130),INDEX(C104:C123,MATCH( C130,A104:A123,0)),"") -- Biff Microsoft Excel MVP "PB" wrote in message ... I MUST BE VERY DUMB AT THIS... PLEASE HELP.. here is my if fuction. =IF(C130=A123,C123,"")&IF(C130=A122,C122,"")&IF(C1 30=A121,C121,"")&IF(C130=A120,C120,"")&IF(C130=A11 9,C119,"")&IF(C130=A118,C118,"")&IF(C130=A117,C117 ,"")&IF(C130=A116,C116,"")&IF(C130=A115,C115,"")&I F(C130=A114,C114,"")&IF(C130=A113,C113,"")&IF(C130 =A112,C112,"")&IF(C130=A111,C111,"")&IF(C130=A110, C110,"")&IF(C130=A109,C109,"")&IF(C130=A108,C108," ")&IF(C130=A107,C107,"")&IF(C130=A106,C106,"")&IF( C130=A105,C105,"")&IF(C130=A104,C104,"").... and so on... I got error saying my formula is too long, how to make this short?? thanks paul |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
(IF FUNTION )HOW TO SHORT THIS FORMULA??
Hi,
Try this: =VLOOKUP(C130,A123:C110,3,) This will return NA if the item is not found. In 2003 you can handle that by =IF(ISNA(VLOOKUP(C130,A123:C110,3,)),"",VLOOKUP(C1 30,A123:C110,3)) adjust references to suit your needs. In 2007 its much easier: =IFERROR(VLOOKUP(C130,A123:C110,3,),"") -- If this helps, please click the Yes button. Cheers, Shane Devenshire "PB" wrote: I MUST BE VERY DUMB AT THIS... PLEASE HELP.. here is my if fuction. =IF(C130=A123,C123,"")&IF(C130=A122,C122,"")&IF(C1 30=A121,C121,"")&IF(C130=A120,C120,"")&IF(C130=A11 9,C119,"")&IF(C130=A118,C118,"")&IF(C130=A117,C117 ,"")&IF(C130=A116,C116,"")&IF(C130=A115,C115,"")&I F(C130=A114,C114,"")&IF(C130=A113,C113,"")&IF(C130 =A112,C112,"")&IF(C130=A111,C111,"")&IF(C130=A110, C110,"")&IF(C130=A109,C109,"")&IF(C130=A108,C108," ")&IF(C130=A107,C107,"")&IF(C130=A106,C106,"")&IF( C130=A105,C105,"")&IF(C130=A104,C104,"").... and so on... I got error saying my formula is too long, how to make this short?? thanks paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to short this substitute formula? | Excel Discussion (Misc queries) | |||
short way for using all sheets in one formula | Excel Worksheet Functions | |||
annual growing rate formula or funtion needed!! | Excel Worksheet Functions | |||
formula to differenciate whether a cell contains a funtion or numb | Excel Worksheet Functions | |||
Formula format for Count or Countif funtion with two criterias | Excel Worksheet Functions |