CODEWITHSUNDEEP
excel
user3348028
user3348028

Reputation: 1

Return the last non-blank value in a row, but only if it matches the column header

Thank you for kindly answering my question "Return the last non-blank value in a row, but only if it matches the column header."

The formula you gave me was:

=IFERROR(LOOKUP(2,1/((D2:H2<>"")*(D$1:H$1="D")),D2:H2),"")

I tried it, but now nothing returns to my End Date column.

Do I need to tell Excel which row the "D" appears (ie., over the last non-blank cell that I want to return)? The "D" headers appear in the row above my data range (ie., row 1).

With Grateful Thanks

~ Jay

Upvotes: 0

Views: 1110

Answers (1)

Floris
Floris

Reputation: 46435

Could it be as simple as

=IFERROR(LOOKUP(1,1/((D2:H2<>"")*(D$1:H$1="D")),D2:H2),"")

                ^ 
                | 
                | changed the 2 to a 1

The expression 1/((D2:H2<>"")*(D$1:H$1)) can only ever be 1 (if both conditions are true) or #DIV0 (when both are false) - so searching for the value 2 in this expression will never work.

Upvotes: 0

Related Questions