Formula to concatenate the last entry of a row with the corresponding column header

[lander2]

Hello

Hoping for some help with this on - I have found and am using the following formula which returns the content of the last entry (left to right) of the row:

=LOOKUP(2,1/(N7:AJ7<>""),N7:AJ7)

I won't claim to understand what this is doing but it works and I have copied and pasted down column AK below the header row which is row 5:

Where I am stuck is that I would like to amend the formula to also return the contents of the corresponding header cell (row 5) which contains a date.
I can concatenate but I'm stuck at pulling the date from row 5 using a formula the can be copied down column AK that will still give the correct results.

Just in case it is relevant, I have a button / macro in place that adds additional columns if needed to the left of Column AK.

The basic layout is

Name	1 Jan 2020	2 Feb 2020	6 Feb 2020	Last Entry
xxxxxxxx	aaaa		bbbbbb	6 Feb 2020 bbbbbb
yyyyyyy		cccccc		2 Feb 2020 cccccc

Thanks for looking
Lindsay

 

[Peter_SSs]

Assuming the values below the dates are text not numerical, try this

20 07 26.xlsm
	N	O	P	Q																			AJ	AK
5	Name	1 Jan 2020	2 Feb 2020	6 Feb 2020																				Last Entry
6	xxxxxxxx	aaaa		bbbbbb																				6 Feb 2020 bbbbbb
7	yyyyyyy		cccccc																					2 Feb 2020 cccccc
Text & Header

Cell Formulas
Range	Formula
AK6:AK7	AK6	=TEXT(LOOKUP(REPT("z",255),O6:AJ6,O$5:AJ$5),"d mmm yyyy ")&LOOKUP(REPT("z",255),O6:AJ6)

 

[Toadstool]

Hi Lander2,

PeterSSs beat me again but taking your original LOOKUP a step further you could try:

Book1
	M	N	O	P	Q																			AJ	AK
6	Name	1-Jan-20	2-Feb-20	6-Feb-20																					Last Entry
7	xxxxxxxx	aaaa		bbbbbb																					06-Feb-20 bbbbbb
8	yyyyyyy		cccccc																						02-Feb-20 cccccc
Sheet1

Cell Formulas
Range	Formula
AK7:AK8	AK7	=TEXT(LOOKUP(2,1/(N7:AJ7<>""),$N$6:$AJ$6),"dd-mmm-yy")&" "&LOOKUP(2,1/(N7:AJ7<>""),N7:AJ7)

 

[Peter_SSs]

taking your original LOOKUP a step further you could try:

The advantage of the LOOKUP(2,1/... approach is that it will pick up the last entry whether text or numeric whereas mine will pick up the last text value only
The disadvantage is that the division algorithm can be a little sluggish if there is a lot of these formulas and a lot columns to divide.,

 

[lander2]

Wow - thank you for such a quick response -
Amazed again. And no need to use Concatenate which I would never have guessed!

Thank you

 

[Peter_SSs]

You're welcome.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Excel 365 with the FILTER function &/or Excel 2019, hereare other ways

20 07 26.xlsm
	N	O	P	Q																			AJ	AK	AL
4																								365	365 or 2019
5	Name	1 Jan 2020	2 Feb 2020	6 Feb 2020																				Last Entry	Last Entry
6	xxxxxxxx	aaaa		bbbbbb																				6 Feb 2020 bbbbbb	6 Feb 2020 bbbbbb
7	yyyyyyy		cccccc																					2 Feb 2020 cccccc	2 Feb 2020 cccccc
Text & Header

Cell Formulas
Range	Formula
AK6:AK7	AK6	=TEXT(MAX(FILTER(O$5:AJ$5,O6:AJ6<>"")),"d mmm yyyy ")&LOOKUP(REPT("z",255),O6:AJ6)
AL6:AL7	AL6	=TEXT(MAXIFS(O$5:AJ$5,O6:AJ6,"<>"),"d mmm yyyy ")&LOOKUP(REPT("z",255),O6:AJ6)