Nested IF Statements

S

Sly929

OK, I need help. In each cell, I seem to only be able to get 8 "IF
statements" in it, is there any way, I can get more into it? here is my
code:
=IF($C2="000","Opening
Balance",IF($C2="001","Contribution",IF($C2="011","Gain or
Loss",IF($C2="020","External Cash Transfer",IF($C2="999","Closing
Balance",IF($C2="002","Withdrawl",IF($C2="005","Transfer
(Inter-Fund)",IF($C2="028","Excess Withdrawls",$C2))))))))

I'm only allowed 8 of those statments, I need to atleast add another 8
more options or "IF" too.
 
P

Peo Sjoblom

Use a 2 column vlookup table with the conditions in the left column and the
result in the adjacent cell
in the right column

=VLOOKUP(C2,D2:E25,2,0)

where the first "000" would start in D2 and "Opening Balance" in E2
and so on with D3 "001" and E3 "Contribution"...

I used D2:E25 but you might be better off putting the table away from view
either
on another sheet or to the far right somewhere..
 
S

Sly929

I tried, but it doesn't work, also additional info is that I am
referencing a different column. example: If column A equals xxx than
place AAA in column b. How can I do that with VLOOKUP
 
P

Peo Sjoblom

What do you mean when you say it doesn't work? If an IF function works where
you are using the same cell
C2 and lookup function will work. The additional info does not mean
anything, you can hard code the
lookup table in the formula

=VLOOKUP(C2,{"000","Opening Balance";"001","Contribution";"011","Gain or
Loss";"020","External Cash"},2,0)

In your if formula you are using "000" which means you are using text not
numbers and if either the lookup or the table differ whereas numbers and
text are concerned, neither the IF nor the lookup function will work
 
S

Sly929

Ok, I got another situation: I have data for the same client on multiple
lines, because each string of raw data contains different transaction
such as Beginning balance. Another string of raw data will contain
Withdrawl, and another string will contain gain/loss and so on. all the
strings pertain to one client. Some clients will have more strings of
raw data depending on the amount of transaction the client has.

So in my spreadsheet, on line A1 I got the beginning balance amount
from the 1st string of raw data, and on line A2 I got a withdrawl from
the 2nd string of raw data, and so on, no I created a second
spreadsheet, I want to combine all the transactions onto one line, but
the problem I am running into is the raw data changes all the time, one
client will have a beginning balance, withdrawl, but no gain/loss,
other might have different transactions such as beginning balance, no
withdrawl, but still have gain/loss, so the raw data will change. I
have ran dry of ideas.....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top