(IF FUNTION )HOW TO SHORT THIS FORMULA??

[PB]

I MUST BE VERY DUMB AT THIS... PLEASE HELP..

here is my if fuction.

=IF(C130=A123,C123,"")&IF(C130=A122,C122,"")&IF(C130=A121,C121,"")&IF(C130=A120,C120,"")&IF(C130=A119,C119,"")&IF(C130=A118,C118,"")&IF(C130=A117,C117,"")&IF(C130=A116,C116,"")&IF(C130=A115,C115,"")&IF(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

 

[T. Valko]

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)),"")

 

[Shane Devenshire]

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(C130,A123:C110,3))

adjust references to suit your needs.

In 2007 its much easier:

=IFERROR(VLOOKUP(C130,A123:C110,3,),"")