How to Find the Last Number in a Series of Non-Adjacent Columns with 1 Exception

Hello,

The columns are:

H648:H668

L648:L668

P648:P668

T648:T668

But the formula also needs to find the highest row number if the columns are of unequal length. For example, if there is data only in cells H648:H663 and L648:L652, the formula would need to select H663.

is this doable?

Thank you,

Patrick

Hi EverClever, this should work for you:-

Please Login or Register  to view this content.

It's an array so you'll have to enter it with Ctrl-Shift-Enter

daddylonglegs,

The answer to your question is P663. It has to be the value in the highest row number and furthest right column letter.

Thanks,

Patrick

Beamermsw,

Thank you for your formula. I entered the formula just for the H row w/ the CTRL-SHIFT-ENTER and the result was incorrect:

{=MAX(IF(H648:H668<>"",ROW(H648:H668)))} = 664 Correct answer is 165 which is the last value and is in Cell H663.

Thanks,

Patrick

daddylonglegs,

The 2 word column headings are all in Rows 645 and 646:

The column headings are #1 in the first row and HR in the second row for column H;

The column headings are #2 in the first row and HR in the second row for column L.

The column headings are #3 in the first row and HR in the second row for column P.

The column headings are #4 in the first row and HR in the second row for column T.

Should I try out your revised formula or wait for you to include the column headings above?

Thanks again,

Patrick

daddylonglegs,

Your revised formula below came back with 0; correct answer still 165


{=LOOKUP(9.99E+307,(""&INDEX(H648:T668,MAX(IF(H646:T646="HR",IF(ISNUMBER(H648:T668),ROW(H648:T668)-ROW(H648)+1))),0))/(H646:T646="HR"))} = 0

Your previous revised formula below came back also with 0 ; correct answer still 165

=LOOKUP(9.99E+307,(""&INDEX(H648:T668,MAX(IF(MOD(COLUMN(H648:T668)-COLUMN(H648),4)=0,IF(ISNUMBER(H648:T668),ROW(H648:T668)-ROW(H648)+1))),0))/(MOD(COLUMN(H648:T668)-COLUMN(H648),4)=0)) = 0


Appreciate your continued help!

Patrick

daddylonglegs,

I am so sorry for the delayed response as life has been hectic the last couple of weeks. Anyway, I wanted to get back to you to say that your revised formula worked and I am gratefully appreciative of all your efforts!

Patrick