If-then-statement

[rise99]

Hello!
I have a spreadsheet 2 columns and 7000 rows. if I have:
column: A column: B
red blue
orange green
... ...
All these "words" repeated in the entire table, I need to do a multiple if statements
If A=red and B=green, then c= OK
if A=orange and B=blue, then c=why
I have like 27 (if - and - to match) and also to search 7000 rows and find these matches??

I need your help plz!!


In the attached file:
I need say : if A="kkkk" and B="VVV" then C="MMM"
from A1:A143, B1:B143 and C1:C143 are repeated "may be randomly" to ~ 7000 rows

in other words I need to complete column C, for the whole worksheet.
Thank you!

[arlu1201]

Your explanation does not match your sample file. What are the conditions you need based on the sample file? I am not sure if you will be able to accomplish 27 matches using a formula, but you can surely get the output you need using code.

[FDibbins]

try putting all of you're search criteria in a table, and use 2 vlookup() to give you what you need. but like arlu said, you're data does not match you're description

[dredwolf]

a "ugly" way of doing it (meaning inelegant and time consuming)

=IF(AND(A1="aaa",B1="bbb"),"ZZZ","")&IF(AND(A1="ccc",B1="ddd"),"YYY","")&IF(AND(A1="eee",B1="fff"),"XXX","").....

just keep adding &IF.. till all the conditions are checked (changing the data to your needs), and copy the formula down
but this is a brute force way, FDibbins suggestion sounds a LOT easier

-Edit-
I have used this approach at times myself, until I could come up with a better way

[arlu1201]

I maybe wrong but you can only use 7 nested ifs, right?

[FDibbins]

not sure if its 7, arlu, but its for sure nowhere near the 27? the OP is looking at? thats why I suggested vlookups and a table

[arlu1201]

I got this info online -

The maximum number of nested IFs for all versions of Excel is 7. This is stated explicitly in the Excel Help topic for "IF worksheet function"...

"Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests."

The outermost IF in the example above would not be counted as a nested IF because it's not contained within another IF function. An explanation of nesting can be found in the Excel Help topic for "About multiple functions within functions, or nesting".

Yeah the vlookups and table is a very good idea otherwise, they have to go in for a vba macro.

[dredwolf]

Yes, the number of NESTED If's is 7, however, these are not nested, just concatenated, so it should work fine
Still, it's an ugly formula, and very difficult to troubleshoot, definitely NOT the best way to solve the problem..

[Fotis1991]

Hi

The limit of 7 if is not exist in Excel>2003(in this case(Excell <=2003) we used helper columns and concatenate all these).

But there are some ways to skip this limit even in Excel<=2003.


Except VLOOKUP_INDEX&MATCH..,there is CHOOSE function(I fi remember well, the limit is 29).

http://www.excelforum.com/excel-form...other-way.html

http://www.excelforum.com/excel-prog...tatements.html

[rise99]

dredwolf :
I used this before and it worked great but for 3 IFs, because there were huge errors when I tried to do the 27!!

[FDibbins]

Rise99 did you see my suggestion?

[dredwolf]

how about an offset,match,match formula like the one in this example ?

-Edit-
this would be a variation of FDibbins table/vlookup suggestion, it's just a way that I've used more than VLookup

-Edit 2-
or example 2 with some basic error checking and a different combination to show how the formula works a little better..

[rise99]

well, I made the description simple so as to know the general idea.The data I use is a Highway data, SR 408, column A and B are the Exits. I want to substitute the exits (from exit A to exit B which is Link 1). It is like that in reality:
IF A= AVI-0498E-Wcolonial_DMS AND B=AVI-0498E-GoodHomesRd THEN C=408E-link1
IF A= AVI-0498E-WBoundary AND B=AVI-0498E-GoodHomesRd THEN C=408E-link2
IF A= AVI-0498E-GoodHomesRd AND B=AVI-0498E-HiawasseeRd THEN C=408E-link3
....
I have ACTUALLY 143 rows (If A= AND B= THEN C= ) that I have to (search and sort out). The purpose of this is to simplify the file so as to use it in other statistical software.

I hope I explained the case!

BTW: English is my second language, I did my best to explain.

[dredwolf]

Okay, just Change the column/slash row headers to your actual conditions, the intersections to the result required...at worst it is a 27x27 table (assuming your original post is accurate), MUCH easier than 27 IF comparisons..
Create the table at the bottom or far right of the spreadsheet, and hide it if you need too..

Not really sure why you are adverse to a table solution, it will make your life a lot easier, and if conditions change, a lot easier to edit..

-Edit-
I used my own sample after seeing in 2 consecutive posts that your example did not meet your Original Post conditions, so, I took the problem the other way and used the" A1 and B1 = certain condition then this " approach,..this is a data does not matter approach, and should work well if you change the example data to yours...