IF Statement

[Libby]

I have a list of 266 budget numbers and I want to create a formula that will
translate the four diget code into the name of the program. I've started with
=IF(A2=1401,"name",IF(A2=2200,"name",IF(A2=2201,"name",IF(A2=2202,"name",IF(A2=2203,"name",IF(A2=2204,"name",IF(A2=2205,"name",IF(A2=2206,"name",0))))))))
But I cannot exceed 8. Suggestions?
I will be pasting this formula into Crystal Reports

 

[Iriemon]

Use VLOOKUP instead:

=VLOOKUP(a2,D2:E267,2)

Place the budget numbers in cells d2 through d267 and the name of the
program in cells e2 through 3267.

 

[Iriemon]

SHOULD SAY:
"program in cells e2 through e267."

Use VLOOKUP instead:

=VLOOKUP(a2,D2:E267,2)

Place the budget numbers in cells d2 through d267 and the name of the
program in cells e2 through 3267.

 

[Libby]

but because I will be pasting it into another program all of the information
needs to be in the one formula, hence If, and not Lookup.

 

[Graeme K Moore]

Libby,
You can use a LOOKUP function that will copy easily.

e.g.

=LOOKUP(A2,{1401,2201,2203...},{Name1,Name2,Name3...})

All you are doing is changing the data from excel references to actual data
arrays.

NOTE: Notice the different perentheses, and make sure that both data arrays
have an equal amount of data within them.

Hope this is more like what you are looking for.

 

[Iriemon]

Is there any way to replace Name1, Name2 etc with formulas rather than text?

I tried this but got an error

=LOOKUP(A2,{1401,2201,2203...},{b2*3,b3*5,b3*7...})

 

[Peo Sjoblom]

Not in a lookup formula you can use CHOOSE and match,




=CHOOSE(MATCH(A2,{1401,2201,2203},0),B2*3,B3*5,B3*7)


will throw an error if no match so you might want to use


=IF(ISNUMBER(MATCH(A2,{1401,2201,2203},0)),CHOOSE(MATCH(A2,{1401,2201,2203},0),B2*3,B3*5,B3*7),"No
Match")




replace "No Match" with a number or a blank if you prefer that


=IF(ISNUMBER(MATCH(A2,{1401,2201,2203},0)),CHOOSE(MATCH(A2,{1401,2201,2203},0),B2*3,B3*5,B3*7),0)


=IF(ISNUMBER(MATCH(A2,{1401,2201,2203},0)),CHOOSE(MATCH(A2,{1401,2201,2203},0),B2*3,B3*5,B3*7),"")






--


Regards,


Peo Sjoblom