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

  • Thread starter Thread starter Onyx_jh
  • Start date Start date
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.
 
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
 
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
 
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
 
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
Click to expand...
 
Hi Guys...

Thanks very much for you help. I have a question too. after aplying Formua If we keep cell "A1" Blank, is there any way that it returns no false or N/A.

Thanks & Regards:bow:
Fairb
 
Back
Top