Look for the content of last non-empty cell in a range and provide a specific value

[Chrispelletier]

Hello,

(I have google a fair bit to help me find an answer but was unsuccessful so I am looking for help.)

I am looking for a formula that would look through a range of cells on a same row and find which one is the last cell of that range to have value in it (i.e. not blank) going horizontaly left to right. Once that is identified, depending on where that last cell was (it which column) it would provide a specific text value.

Here a little more details:
-The range for the first row would be from column C to column F (C1:F1) and the result of the formula would be in column G (G1)
-If the first cell (C1) is the last cell in the C1 to F1 range to have value in it then the value entered in G1 would be ‘Step #1’;
-If the 2nd cell (D1) is the last cell in the C1 to F1 range to have value in it then the value entered in G1 would be ‘Step #2’; and so on up to step #4
-All value in the cells are text value.

I have attaching an example to better illustrate what I am looking for. Thank you in advance for your time!

[FDibbins]

Im a little confused as to what you want here.

If you want to return the text in the last-filled cell in the row...
=INDEX(C1:Z1,MATCH("*",C1:Z1,-1))

If you want to deterine how many cells contain data...
="Step #"&COUNTA(C1:F1)

[Chrispelletier]

Hello,

Thank you for your quick response and sorry I was not clear enough. I am not looking for to return the text in the last-filled cell in the range nor to determine how many cells contain data. Did you have a change to look at the attached example in post #1 which may help?

Let me try to explain in a slightly different way:
I am looking for the formula to identified the last-filled cell in the range (For example C1:F1). Once that is done:
if the last filled cell was in that range was in column C, then return the text value 'Step #1' in cell G1 or
if the last filled cell was in that range was in column D, then return the text value 'Step #2' in cell G1 or
if the last filled cell was in that range was in column E, then return the text value 'Step #3' in cell G1 or
if the last filled cell was in that range was in column F, then return the text value 'Step #3' in cell G1

I hope this clarify what I am looking for. Thank you!

[Ace_XL]

Try

="Step #"&MAX(INDEX((C1:F1<>"")*(ROW(C1:F1)),0))

[Chrispelletier]

Try

="Step #"&MAX(INDEX((C1:F1<>"")*(ROW(C1:F1)),0))

Thank you for your response. I tried it and it work for the first 4 rows but after that the formula did not work for the rest of the data that I added. It should not be higher than step #4 as there is only 4 column for the 'ranking' (column C,D,E &F). I have added another example workbook to show the results of your formula and the results that I expect.

Thank you!

[Ace_XL]

My bad..it should be

="Step #"&MAX(INDEX((C1:F1<>"")*(COLUMN(C1:F1)-COLUMN(C:C)+1),0))

[FDibbins]

Hello,

Thank you for your quick response and sorry I was not clear enough. I am not looking for to return the text in the last-filled cell in the range nor to determine how many cells contain data. Did you have a change to look at the attached example in post #1 which may help?

Thats exactly what my 2nd fformula does for you...

	C	D	E	F	G
1	abc				Step #1
2	abc	abc			Step #2
3	abc	acb	abc		Step #3
4	abc	abc	abc	abc	Step #4

G1="Step #"&COUNTA(C1:F1) copied down

So unless you have blank cells within your data (ie C4, C5=data, C6=blanl, C7=data), that will do what you need. If it is not working for you, please show me where it isnt working)

[Chrispelletier]

Fdibbins: Terribly sorry, your 2nd formula does work, I should have tried it at the beginning but the title let me to believe the results would not be want I wanted but I was wrong.

Ace_XL: You revised formula work as well. Thank you for your time!

[FDibbins]

Not a problem, it happens Im just glad we were able to resolve your questio, and thanks for the feedback