Lookup Table/column Header based on Last Row of Data (first blank value found)

[GauzeD]

Hello...been forever since I have posted here but I have hit a roadblock and the fine folks at MrExcel always come through

See sample table below...I am attempting to find a formula that applies the column header (dates) into the LastDate column based on the last data entry for each person (represent hours per for each month). The end game is to find out when each persons last date of project work is supposed to be. The expected results were manually put into the LastDate column as shown below. I have hundreds of rows in the actual file I figured out the formula to find the cell that HAS the last row of data (using INDEX MATCH and ISBLANK), but I am unsure how to then also retrieve the applicable date header 'above' (so to speak).

Any help is greatly appreciated. Thanks.

Name/Date	31-Jan-25	28-Feb-25	31-Mar-25	30-Apr-25	31-May-25	30-Jun-25	Last Date
Joe	10	10	5	5			30-Apr-25
Jen	20	20	20	20	20	20	30-Jun-25
Tim	10						31-Jan-25
John	5	5	5				31-Mar-25
Bill	20	20					28-Feb-25

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Name/Date	31/01/2025	28/02/2025	31/03/2025	30/04/2025	31/05/2025	30/06/2025	Last Date
2	Joe	10	10	5	5			30/04/2025
3	Jen	20	20	20	20	20	20	30/06/2025
4	Tim	10						31/01/2025
5	John	5	5	5				31/03/2025
6	Bill	20	20					28/02/2025
Sheet5

Cell Formulas
Range	Formula
H2:H6	H2	=LOOKUP(10^9,B2:G2,$B$1:$G$1)

 

[GauzeD]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Name/Date	31/01/2025	28/02/2025	31/03/2025	30/04/2025	31/05/2025	30/06/2025	Last Date
2	Joe	10	10	5	5			30/04/2025
3	Jen	20	20	20	20	20	20	30/06/2025
4	Tim	10						31/01/2025
5	John	5	5	5				31/03/2025
6	Bill	20	20					28/02/2025
Sheet5

Cell Formulas
Range	Formula
H2:H6	H2	=LOOKUP(10^9,B2:G2,$B$1:$G$1)

So close! Thanks. What if a name doesn't have any hours listed? I am currently getting an #NA error in those columns. Slapping IF(ISNA(...) around the formula seems to work, but if you have a better/easier way, I'm all ears

 

[Fluff]

How about

=IFNA(LOOKUP(10^9,B2:G2,$B$1:$G$1),"")

Also please respond to either of the two direct messages you have received.

 

[GauzeD]

Sorry, clicked 'Reply' in your response. Still re-learing the MrExcel way

Well look at that...IFNA. Kind of like XLOOKUP building the 'if you get an #NA...' into it's formula. One post in and I'm already learning something new Ok, last question...I assume the '10^9' in the formula has something to do with looking for the first blank row. Can you explain how that works?

PS...funny how I've learned H/VLOOKUP then graduated to XLOOKUP, only to have to go back to old school LOOKUP

 

[Fluff]

The 10^9 is just a large number so that lookup will find the last value in the row.

 

[GauzeD]

Right...because LOOKUP is looking for the 'largest' value (educating myself as we speak). Perfect. This is great. I think I have what I need. Really appreciate the help. Thanks!

 

[Fluff]

Glad to help & thanks for the feedback.

As requested earlier, please respond to either of the two Direct Messages that you have received regarding your account.