How many if's?

[Guest]

How many "if" statements can I include into each formula? I can only seem to
get about 9 and then it gives me an error message. Is there a better way to
do conditional statements that have several conditions?

Thanks

Christopher

 

[Aladin Akyurek]

More often than not, tasks that seemingly need a chain of (nested) IFs
can be re-expressed as a lookup task.

 

[Guest]

How would a lookup statement work in this situation. If I have a column that
has various text strings in it (ex. ABC, CDF, DFE, FEG...) and I want to
assign a numeric value in a separate column to all ABC's, CDF's, DFE's, etc.
I tried this with a nested if statement but it would not allow me to do more
than 8 nested if statements. I tried a lookup function but I am not familiar
enough with these to know if what I did was correct. What would you suggest?
Perhaps give me an example of the syntax.

Thanks

Christopher

 

[Aladin Akyurek]

B1:

=LOOKUP(A1,{"ABC","CDF","DFE"},{3,5,2.6})

The formula uses a 2-column built-in table, sorted in ascending order on
its first column, which associates strings with numerical values.

Note that A1 must not have a string that does not occur in the
(built-in) table.

Note also that you can have the table in some range, name it Table, and use:

=LOOKUP(A1,Table)

 

[Max]

An example using VLOOKUP ..

Assume you have set up a reference table

In Sheet1, in A1:B4
-------------
ABC 1
CDF 2
DFE 3
FEG 4

then, if you have

In Sheet2
---------
the listing below in A1 downwards

CDF
DFE
FEG
ABC
CDF

you could put in B1: =VLOOKUP(A1,Sheet1!A:B,2,0)
and copy down

Col B will return the numbers associated with the names
(as per the reference table in Sheet1)

Or, perhaps better with an error trap included
put instead in B1:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"",VLOOKUP(A1,Sheet1!A:B,2,0))

Copy down

With the error trap, any unmatched names in col A
will now return blanks: "", instead of #NAs