IF statement with multiple conditions, in sequence

[Ken Soona]

Hello everyone,

I need to find a 'best' value from a series of values, based on a ranking, and need to write an IF statement to choose, in a specific order.

I have 3 columns with dates of birth, DOB1, DOB2, DOB3. Not all columns are populated for each row.

I need to fill in a 4th column, DOB_final, with a DOB, based on the ranking of

DOB1 > DOB2 > DOB3

I need to write an IF statement for DOB_final that says

IF DOB1 is not blank, DOB_final = DOB1
IF DOB1 is blank, DOB_final = DOB2
IF DOB1 and DOB2 are blank, DOB_final = DOB3

I am trying to look at IF / AND statements, n cannot find an example or think of one that'll do what I need.

Can anyone help please ?

 

[Ken Soona]

Follow up:

Why is this not working?

DOB1 = A1
DOB2 = B1
DOB3 = C1

I wrote a series of IF statements along the lines of:

IF(A1<>"",A1,IF(A1="",B1,IF(AND(A1="",B1=""),,C1)))

BUT....

When A1 is populated, I have to write:
IF(A1<>"",A1,IF(A1=" ",B1,IF(AND(A1=" ",B1=" "),,C1)))

When A1 and B1 are blank but C1 is populated, I have to write:
IF(A1<>"",A1,IF(A1=" ",B1,IF(AND(A1=" ",B1=" "),,C1)))

When A1 and C1 are blank, but B1 is populated, I have to write
IF(A1<>"",A1,IF(A1="",B1,IF(AND(A1=" ",B1=" "),,C1)))

...the difference is a single space after the 2nd IF. All data are stored as text, they have to be that way.

Why do I need that extra space? And, more important question, how do I work around it? I assume it's got something to do with text formatting, but isn't a blank cell a blank cell a blank cell?

 

[jtakw]

Hi,

Will A1 and B1 be both populated? or A1, B1, C1 all populated?

 

[Ken Soona]

At least 1 of the cells will be populated in each row, it could be 1, or 2, or all 3.

I need to write something says "look in cells A1, B1, and C1, in order A1>B1>C1, and put the result in cell D1"

 

[jtakw]

So if all 3 are populated, do you want A1 or C1?

 

[Ken Soona]

So if all 3 are populated, do you want A1 or C1?

I need the formula to look at all 3 cells, and take the value in order of

A1 > B1 > C1

The result will always take A1 if A1 is present, if not, it looks at B1, if not B1, it looks at C1, but it goes in that rank order.

I think my problem is that the data in cells A1, B1, and C1 are formatted as text, that's the thing. But they have to be formatted as text, so I don't know what else to do.

 

[dreid1011]

Perhaps something like this:​

=IF(A1<>"",A1,IF(B1<>"",B1,C1))

 

[jtakw]

You should be able to just do this, I have A1:C1 formatted as TEXT, but can't replicate your problem:

Book1
	A	B	C	D
1	1/1/1111	1/1/1112	1/1/1113	1/1/1111
Sheet5

Cell Formulas
Range	Formula
D1	=IF(A1<>"",A1,IF(B1<>"",B1,C1))

Edit, Didn't see dreid already gave you the same formula when I upped this post.