Is there any way around the maximum of seven nested functions?

[Guest]

I am working on an Excel spreadsheet, but the summary page must check the
information in 10 to 20 cells and once it finds a match, it must become equal
to a value in a different cell. Is there any way to have multiple IF
functions to verify a value when there are more than 7 checks (the maximum
number of nested functions)? FYI, I am comparing data strings.

 

[Guest]

more info,- what where?


"Nakia Allen" skrev:

 

[RagDyeR]

There are ways to construct the IF() function to reference more then 7
arguments, but the wiser choice is to use other functions (Lookup, Vlookup)
instead.

Why not describe what you're trying to accomplish so that you might get
suggestions here on how to perhaps better approach a solution.

If you wish, check out this link to see a way around the 7 argument limit:

http://www.cpearson.com/excel/nested.htm


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I am working on an Excel spreadsheet, but the summary page must check the
information in 10 to 20 cells and once it finds a match, it must become
equal
to a value in a different cell. Is there any way to have multiple IF
functions to verify a value when there are more than 7 checks (the maximum
number of nested functions)? FYI, I am comparing data strings.

 

[Arvi Laanemets]

Hi

Possible ways:

1. Use CHOOSE (in combination with MATCH, or with some numeric expression.
Up to 28 options are available.

2. Use VLOOKUP/HLOOKUP with lookup table or with array argument for lookup
values.

3. When the result will be number, you can compose the formula in form
=LogicalExpression1*Result1+LogicalExpression2*Result2+...+LogicalExpression
N*ResultN


Arvi Laanemets

 

[CLR]

VLOOKUP might be an appropriate replacement for the IF.........

Vaya con Dios,
Chuck, CABGx3

 

[patele]

Yes you can get around the 7 nested if's. One way is the CONCATENATE
function which gives you about 30 comparisons an example:

=CONCATENATE(IF(A1="A",1,""), IF(A1="B",2,""), IF(A1="C",3,"")

Another way is using the concatenation operator (&) for example:

=IF(A1="A",1,"") &IF(A1="B",2,"") &IF)A1="C",3,"")

Using the (&) operator will not limit you to 30 comparisons. For all
practical purposes it's unlimited.