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

lander2

New Member
Joined
May 4, 2017
Messages
24
Office Version
  1. 2010
Platform
  1. Windows
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

Name1 Jan 20202 Feb 20206 Feb 2020Last Entry
xxxxxxxxaaaabbbbbb6 Feb 2020
bbbbbb
yyyyyyycccccc2 Feb 2020
cccccc



Thanks for looking
Lindsay
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Assuming the values below the dates are text not numerical, try this

20 07 26.xlsm
NOPQAJAK
5Name1 Jan 20202 Feb 20206 Feb 2020Last Entry
6xxxxxxxxaaaabbbbbb6 Feb 2020 bbbbbb
7yyyyyyycccccc2 Feb 2020 cccccc
Text & Header
Cell Formulas
RangeFormula
AK6:AK7AK6=TEXT(LOOKUP(REPT("z",255),O6:AJ6,O$5:AJ$5),"d mmm yyyy ")&LOOKUP(REPT("z",255),O6:AJ6)
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,300
Office Version
  1. 2016
Platform
  1. Windows
Hi Lander2,

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

Book1
MNOPQAJAK
6Name1-Jan-202-Feb-206-Feb-20Last Entry
7xxxxxxxxaaaabbbbbb06-Feb-20 bbbbbb
8yyyyyyycccccc02-Feb-20 cccccc
Sheet1
Cell Formulas
RangeFormula
AK7:AK8AK7=TEXT(LOOKUP(2,1/(N7:AJ7<>""),$N$6:$AJ$6),"dd-mmm-yy")&" "&LOOKUP(2,1/(N7:AJ7<>""),N7:AJ7)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 4, 2017
Messages
24
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
NOPQAJAKAL
4365365 or 2019
5Name1 Jan 20202 Feb 20206 Feb 2020Last EntryLast Entry
6xxxxxxxxaaaabbbbbb6 Feb 2020 bbbbbb6 Feb 2020 bbbbbb
7yyyyyyycccccc2 Feb 2020 cccccc2 Feb 2020 cccccc
Text & Header
Cell Formulas
RangeFormula
AK6:AK7AK6=TEXT(MAX(FILTER(O$5:AJ$5,O6:AJ6<>"")),"d mmm yyyy ")&LOOKUP(REPT("z",255),O6:AJ6)
AL6:AL7AL6=TEXT(MAXIFS(O$5:AJ$5,O6:AJ6,"<>"),"d mmm yyyy ")&LOOKUP(REPT("z",255),O6:AJ6)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,052
Messages
5,599,511
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top