How to return the top row value of the column that is after the last blank cell in a row?

[charbons]

Hello,

Looking for support coding a formula that will return the value in the top row for the column that has value after the last blank of a row. To explain what I mean, here's a screen shot with the expected results:

Screenshot 2022-05-03 101705.png

Thanks!

SC

[Fluff13]

How about

Please Login or Register  to view this content.

[charbons]

Yeah tried that, the last 3 rows return "Jerry" because it's the last column with data. It works for Suzanne and "#N/A" though.

[AliGW]

Try this:

=LOOKUP(2,1/(A3:H3<>""),$B$1:$I$1)

[charbons]

Tried that one as well, first row returns Jerry, second #N/A and last 3 are blanks. Also tried LOOKUP(2,1/(A3:H3=""),$B$1:$I$1) and it works for Monic and Pierre but rest is incorrect.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[charbons]

Here's the sample Excel doc (with expected result) to try formulas.

[AliGW]

Sorry - I am not fully understanding your logic. I think you are going to need to tell us why you want the answers you say you want where the expected results do not actually match your description in post #1.

[Bo_Ry]

Please try

=LET(z,A3:H3,y,$A$1:$H$1,IFERROR(INDEX(y,1+COLUMNS(z)-FIND(10,CONCAT(SORTBY(IF(z>0,1,0),COLUMN(z),-1))&0)),""))

or
=IFNA(LOOKUP(2,1/(A3:G3<>B3:H3)/(A3:G3=""),$B$1:$H$1),IF(COUNTA(B3:H3),$A$1,""))

[charbons]

I'm sorry, not sure I understand your response AliGW. The contents of the file I uploaded "Sample.xlsx" mirrors the image I attached in the original post, they are absolutely identical I triple-checked.

Now, here's another way of describing what I would like the formula to do: For every row I want to identify the column after the last blank in the row and return that column's top row value. In the image and in the sample file, I manually inputted the response I expect from the formula in the last column.

Thank you so much for looking into this and for all your help.

[AliGW]

I'm sorry, not sure I understand your response AliGW.

I don't fully follow your logic - I asked for the workbook just so that I didn't have to faff about recreating data that you already had, and I said nothing about anything having changed.

When you are close to your data, you know exactly what you want: sometimes that isn't completely obvious to others, so sometimes you have to be prepared to elaborate a bit.

Anyway, you have a solution in post #9.

For every row I want to identify the column after the last blank in the row and return that column's top row value

Two of the rows clearly need further explanation, as they do not fit this overarching requirement.

[charbons]

please try

=let(z,a3:h3,y,$a$1:$h$1,iferror(index(y,1+columns(z)-find(10,concat(sortby(if(z>0,1,0),column(z),-1))&0)),""))

or
=ifna(lookup(2,1/(a3:g3<>b3:h3)/(a3:g3=""),$b$1:$h$1),if(counta(b3:h3),$a$1,""))

that's it!!!! Thank you

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.