Returning value of a column header from last instance of a cell value

iksotof
3StarLounger
Posts: 315
Joined: 04 May 2010, 15:18

Returning value of a column header from last instance of a cell value

Post by iksotof »

Hi

I have a sheet with column headers of dates (left most historic to right most recent) against a column for names. In each row name, I denote under the corresponding date column when the client fulfilled an appointment with the simple text of "review". Over time there are mutliple reviews. I need to establish in one column adjacent to the each client row when the last "review" took place. I have attached an example sheet, using the first entry, the most recent review was under Jun 23 column (yellow), the second row client is in Jul 23 (dark blue), third row is Aug 23 (light blue). My ideal solution would be in the column headed "Last Review" to pull through the respective last review date for that eow client, as in the attached examnple.

Any help would be appreciated.


Thank you
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78678
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Returning value of a column header from last instance of a cell value

Post by HansV »

In B2:

=LOOKUP("z",$C2:$T2,$C$1:$T$1)

Fill down.
Best wishes,
Hans

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: Returning value of a column header from last instance of a cell value

Post by hamster »

try Power Query
You do not have the required permissions to view the files attached to this post.

snb
4StarLounger
Posts: 596
Joined: 14 Nov 2012, 16:06

Re: Returning value of a column header from last instance of a cell value

Post by snb »

=EDATE($C$1;MAX(($C2:$T2="Review")*COLUMN($A:$R))-1)

iksotof
3StarLounger
Posts: 315
Joined: 04 May 2010, 15:18

Re: Returning value of a column header from last instance of a cell value

Post by iksotof »

Thank you both, these help and I certainly can use going forwards, I need a bit more work on Power Queries :)

Apologies hwever, I didn't define the problem sufficiently, there are other entries in the sheet, I have uploaded a revised example. As you can see I have
"other" entries along the way too, it is the last occurence of "Review" with its corresponding date I am looking for please.

Again your help is most appreciated.

Thank you
Darren
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78678
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Returning value of a column header from last instance of a cell value

Post by HansV »

You can use

=LOOKUP(2,1/($C2:$T2="Review"),$C$1:$T$1)
Best wishes,
Hans

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: Returning value of a column header from last instance of a cell value

Post by hamster »

just for fun again with Power Query :thumbup:
You do not have the required permissions to view the files attached to this post.