nesting 18 x functions
 

Help, anyone?
I am trying to nest 18 x IF functions to automatically pick up a text value
for a numeric result. I have ranked 18 x actual values in 3 x cell in a
column for my top 3 x performers (value of sales). what I now want to do is
on the next row have the name of that person appear under that specific
ranking number according to the actual sales (so I don't need to scroll up to
see who produced that value). Nesting only allows me 7 x IF's. Help anyone
???

Hi - whilst you can only nesti 7 IF statements, you CAN do this by NOT
nesting eg if your IF statement with nesting was like this:

=IF(A1=1,2,if(A1=2,3 etc etc etc
this can also be written as:

=IF(A1=1,2,"") & IF(A1=2,3,"") & IF(A1 etc etc etc

to return a VALUE, you would need to enclose the above with VALUE() eg

=VALUE(IF(A1=1,2,"") & IF(A1=2,3,"") & IF(A1=3,4,""))

This type of formula has no limit to the number of functions but IS
restricted by the length of text a formula can hold (but you should be ok
here)

However, this type of formula is inefficient and from what you describe, you
may be better off investigating VLOOKUP or a combination of INDEX / MATCH

HTH
Rgds
Geoff

"Jenny" wrote:

Help, anyone?
I am trying to nest 18 x IF functions to automatically pick up a text value
for a numeric result. I have ranked 18 x actual values in 3 x cell in a
column for my top 3 x performers (value of sales). what I now want to do is
on the next row have the name of that person appear under that specific
ranking number according to the actual sales (so I don't need to scroll up to
see who produced that value). Nesting only allows me 7 x IF's. Help anyone
???

You wouldn't use IF for this you would use something like VLOOKUP, eg:-

assuming your table was in say A1:B9

A B
1 101 abc
2 102 bcd
3 103 cde
4 104 def
5 105 efg
6 106 fgh
7 107 ghi
8 108 hij
9 109 ijk

and in say cell D1 you had the value of 104 and in E1 you wanted the
corresponding value from col B that matched that value in Col A.

In cell E1 you would simply use the formula

=VLOOKUP(D1,A1:B9,2,0)

which says, take the value in D1, look it up in the leftmost column of the
range A1:B9 and then when you match it exactly (0 at end denotes exact match)
give me the value from the 2nd column (Hence the 2), in that range.

Regards
Ken...................




"Jenny" wrote:

Help, anyone?
I am trying to nest 18 x IF functions to automatically pick up a text value
for a numeric result. I have ranked 18 x actual values in 3 x cell in a
column for my top 3 x performers (value of sales). what I now want to do is
on the next row have the name of that person appear under that specific
ranking number according to the actual sales (so I don't need to scroll up to
see who produced that value). Nesting only allows me 7 x IF's. Help anyone
???

Hi

Another option, when the number of possible values doesn't exceed 24 and the
lookup table doesn't change in futu

=(CHOOSE(MATCH(D1,{101,102,103,104,105,106,107,108 ,109},1),"abc","bcd","cde"
,"def","efg","fgh","ghi","hij","ijk")


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)



"Ken Wright" wrote in message
...
You wouldn't use IF for this you would use something like VLOOKUP, eg:-

assuming your table was in say A1:B9

A B
1 101 abc
2 102 bcd
3 103 cde
4 104 def
5 105 efg
6 106 fgh
7 107 ghi
8 108 hij
9 109 ijk

and in say cell D1 you had the value of 104 and in E1 you wanted the
corresponding value from col B that matched that value in Col A.

In cell E1 you would simply use the formula

=VLOOKUP(D1,A1:B9,2,0)

which says, take the value in D1, look it up in the leftmost column of the
range A1:B9 and then when you match it exactly (0 at end denotes exact
match)
give me the value from the 2nd column (Hence the 2), in that range.

Regards
Ken...................




"Jenny" wrote:

Help, anyone?
I am trying to nest 18 x IF functions to automatically pick up a text
value
for a numeric result. I have ranked 18 x actual values in 3 x cell in a
column for my top 3 x performers (value of sales). what I now want to
do is
on the next row have the name of that person appear under that specific
ranking number according to the actual sales (so I don't need to scroll
up to
see who produced that value). Nesting only allows me 7 x IF's. Help
anyone
???