IF Statement

  • Thread starter Thread starter Libby
  • Start date Start date
L

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
 
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.
 
SHOULD SAY:
"program in cells e2 through e267."


Iriemon said:
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.
Click to expand...
 
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.
 
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.
 
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...})
 
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
 
Back
Top