nesting 18 x functions

[Jenny]

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
???

 

[xlbo]

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

 

[Ken Wright]

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...................

 

[Arvi Laanemets]

Hi

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

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