HELP. I need an "IF" formula with a lot of information

O

Onyx_jh

I am trying to build an "IF" formula with a lot of parameters. e.g.
=IF(A1="CAT",12.5,IF(A1="DOG", 50.6,IF(... Just continue that on
for most of the animals out there to see the size I'm going for. I can get
up to about 7 entries and I get the message "The specified formula cannot be
entered because it uses more levels of nesting than are allowed in the
current file format". I don't know if I need to split the formula some how
or just what. If possible I do need these in the same function. Any help or
ideas/suggestions would deeply appreciated. Thank you.
 
T

Tom Hutchins

Build a lookup table. On another sheet (Sheet3 in my example), enter the list
of animals in column A and the value to return for each next to it in column
B. Then, on the sheet where you are specifying an animal in A1, enter this
formula in the cell where you want the value returned:

=IF(ISERROR(VLOOKUP(A1,Sheet3!A:B,2,FALSE)),"Not
found",VLOOKUP(A1,Sheet3!A:B,2,FALSE))

This formula is using Vlookup to find the A1 animal in the table on Sheet3
and return the value next to it. I have wrapped the Vlookup in an IF(ISERROR(
construction so if the animal is not found on Sheet3, "Not found" is returned
instead of a #N/A error. Change Sheet3 to whatever sheet name you use, and
change "Not found" to some other text (or a number) if desired. When you have
new animals, you just add them to the lookup table on Sheet3.

Hope this helps,

Hutch
 
K

Kassie

You could create a list of animals with corresponding numbers, such as in A1
Cat, B1 12,5, A2 Dog, B2 50,6. Carry on down as far as required, then name
this list say Animals

Now where you want these numbers to appear, use a VLOOKUP statement.

Say you have Dog in Sheet2! A12, and you want the corresponding value to
show in E12, then in E12 enter
=IF(A12="","",VLOOKUP(A12,Animals,2,0))
--
HTH

Kassie

Replace xxx with hotmail
 
B

Bob Umlas

Put all those conditions in cells, then use VLOOKUP. Example
K L
1 CAT 12.5
2 DOG 50.6
3 BIRD 22.7

and use a formula like =VLOOKUP(A1,K1:L100,2,FALSE)

Bob Umlas
Excel MVP
 
O

Onyx_jh

That works awesome and perfect. Thank you all.

Tom Hutchins said:
Build a lookup table. On another sheet (Sheet3 in my example), enter the list
of animals in column A and the value to return for each next to it in column
B. Then, on the sheet where you are specifying an animal in A1, enter this
formula in the cell where you want the value returned:

=IF(ISERROR(VLOOKUP(A1,Sheet3!A:B,2,FALSE)),"Not
found",VLOOKUP(A1,Sheet3!A:B,2,FALSE))

This formula is using Vlookup to find the A1 animal in the table on Sheet3
and return the value next to it. I have wrapped the Vlookup in an IF(ISERROR(
construction so if the animal is not found on Sheet3, "Not found" is returned
instead of a #N/A error. Change Sheet3 to whatever sheet name you use, and
change "Not found" to some other text (or a number) if desired. When you have
new animals, you just add them to the lookup table on Sheet3.

Hope this helps,

Hutch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top