Formula for finding last occurrence of a matched value and displaying the first word of a corresponding cell

[AB1023]

Hello!

I am a bit stumped here. I am working on a workbook with 2 worksheets and I need to figure out 2 formulas:

Worksheet 1 contains (among other data):

A	N	O	P
Participant name	Month Started (First Session) <tbody> </tbody>	Month Completed (Month of Last Session)	Current Session # Complete
Marla Kouche	February	September	6

<tbody>
</tbody>

Worksheet 2 ('Admin Tracker') contains (among other data):

A Date session occurred​ <colgroup><col></colgroup><tbody> </tbody>	B Participant​ <colgroup><col style="text-align: center;"></colgroup><tbody> </tbody>	E Session Complete <colgroup><col></colgroup><tbody> </tbody>
February 08, 2016 16:00 CST <colgroup><col></colgroup><tbody> </tbody>	Charlene Romero <colgroup><col></colgroup><tbody> </tbody>	1 <colgroup><col></colgroup><tbody> </tbody>
February 08, 2016 16:00 CST <colgroup><col></colgroup><tbody> </tbody>	Ashley Clark <colgroup><col></colgroup><tbody> </tbody>	1 <colgroup><col></colgroup><tbody> </tbody>
February 09, 2016 12:00 CST <colgroup><col></colgroup><tbody> </tbody>	Autumn Church <colgroup><col></colgroup><tbody> </tbody>	1 <colgroup><col></colgroup><tbody> </tbody>
February 10, 2016 12:00 CST <colgroup><col></colgroup><tbody> </tbody>	Jeffery Logan <colgroup><col></colgroup><tbody> </tbody>	1 <colgroup><col></colgroup><tbody> </tbody>
February 11, 2016 09:00 CST <colgroup><col></colgroup><tbody> </tbody>	Matt Barondick <colgroup><col></colgroup><tbody> </tbody>	1 <colgroup><col></colgroup><tbody> </tbody>
April 11, 2016 15:00 CST <colgroup><col></colgroup><tbody> </tbody>	Jill Deen <colgroup><col></colgroup><tbody> </tbody>	1 <colgroup><col></colgroup><tbody> </tbody>
February 17, 2016 13:00 CST <colgroup><col></colgroup><tbody> </tbody>	Marla Kouche <colgroup><col></colgroup><tbody> </tbody>	1 <colgroup><col></colgroup><tbody> </tbody>
February 08, 2016 16:00 CST <colgroup><col></colgroup><tbody> </tbody>	Ashley Clark <colgroup><col></colgroup><tbody> </tbody>	2 <colgroup><col></colgroup><tbody> </tbody>
February 20, 2016 16:00 CST <colgroup><col></colgroup><tbody> </tbody>	Ashley Clark <colgroup><col></colgroup><tbody> </tbody>	3 <colgroup><col></colgroup><tbody> </tbody>
March 09, 2016 12:00 CST <colgroup><col></colgroup><tbody> </tbody>	Autumn Church <colgroup><col></colgroup><tbody> </tbody>	2 <colgroup><col></colgroup><tbody> </tbody>
February 10, 2016 12:00 CST <colgroup><col></colgroup><tbody> </tbody>	Jeffery Logan <colgroup><col></colgroup><tbody> </tbody>	3 <colgroup><col></colgroup><tbody> </tbody>
February 11, 2016 15:00 CST <colgroup><col></colgroup><tbody> </tbody>	Matt Barondick <colgroup><col></colgroup><tbody> </tbody>	1 <colgroup><col></colgroup><tbody> </tbody>
September 17, 2016 13:00 CST <colgroup><col></colgroup><tbody> </tbody>	Marla Kouche <colgroup><col></colgroup><tbody> </tbody>	6 <colgroup><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

I need to figure out how to automatically calculate the values in Worksheet 1 (Columns N, O, & P) based on the data provided in Worksheet 2:

For:

Column N I have already figured out the following formula that seems to be working just fine:

=LEFT((IF(NOT(ISERROR(INDEX('Admin Tracker'!$A$4:$A$698,MATCH(A38,'Admin Tracker'!$B$4:$B$698,0)))),(INDEX('Admin Tracker'!$A$4:$A$698,MATCH(A38,'Admin Tracker'!$B$4:$B$698,0))))),FIND(" ",(IF(NOT(ISERROR(INDEX('Admin Tracker'!$A$4:$A$698,MATCH(A38,'Admin Tracker'!$B$4:$B$698,0)))),(INDEX('Admin Tracker'!$A$4:$A$698,MATCH(A38,'Admin Tracker'!$B$4:$B$698,0))))))-1)



Column O I don't know how to find the date the participant completed all 6 sessions (meaning last participants entry, but only if it equals session 6)

I tried something similar to this but it didn't work:

IF((LOOKUP(A38,'Admin Tracker'!B4:B700,'Admin Tracker'!E4:E700))=6,(LOOKUP(A38,'Admin Tracker'!B4:B700,'Admin Tracker'!A4:A700)),0)



Column P This needs to always reflect the most recent session completed for that participant


To note: The participant will only be named once on Worksheet 1, but up to 6 times on Worksheet 2

HELP! Thanks

 

[DILIPandey]

Hi AB1023,

" Dates Session Occurred" field :-
1) has date in date format ?
2) has date sorted in ascending order?


Regards,
DILIPandey

 

[DILIPandey]

Okay... based on my point 2), use below formula in cell

n2:-

=INDEX('Admin Tracker'!$A$1:$A$14,MIN(IF('Admin Tracker'!$B$2:$B$14=Sheet2!$A2,ROW('Admin Tracker'!$A$2:$A$14),"")))

o2:-

=INDEX('Admin Tracker'!$A$1:$A$14,MAX(IF('Admin Tracker'!$B$2:$B$14=Sheet2!$A2,ROW('Admin Tracker'!$A$2:$A$14),"")))

p2:-
=INDEX('Admin Tracker'!$E$1:$E$14,MAX(IF('Admin Tracker'!$B$2:$B$14=Sheet2!$A2,ROW('Admin Tracker'!$A$2:$A$14),"")))

Confirm above formulas with key combination ctrl shift enter not just enter and drag down.



Regards,
DILIPandey

 

[AB1023]

Thank you for the response! Unfortunately, the formulas for o2 and p2 are still referencing the first occurrence of a participants session and not the last.

Also, what does the ctrl shift enter do that a normal enter would not normally do? I had not seen that before!

Okay... based on my point 2), use below formula in cell

n2:-

=INDEX('Admin Tracker'!$A$1:$A$14,MIN(IF('Admin Tracker'!$B$2:$B$14=Sheet2!$A2,ROW('Admin Tracker'!$A$2:$A$14),"")))

o2:-

=INDEX('Admin Tracker'!$A$1:$A$14,MAX(IF('Admin Tracker'!$B$2:$B$14=Sheet2!$A2,ROW('Admin Tracker'!$A$2:$A$14),"")))

p2:-
=INDEX('Admin Tracker'!$E$1:$E$14,MAX(IF('Admin Tracker'!$B$2:$B$14=Sheet2!$A2,ROW('Admin Tracker'!$A$2:$A$14),"")))

Confirm above formulas with key combination ctrl shift enter not just enter and drag down.



Regards,
DILIPandey

 

[DILIPandey]

I got the correct results.

have you entered formula with ctrl shift Enter key combination ?


Regards,
DILIPandey

 

[AB1023]

I did yes. I made a slight adjustment to the formula and it now seems to be working:

=INDEX('Admin Tracker'!$A$2:$A$700,MAX(IF('Admin Tracker'!$B$2:$B$700=A2,ROW('Admin Tracker'!$B$2:$B$700)-3,-1)))

Why are array formulas different and require the ctrl shift enter strokes?

I got the correct results.

have you entered formula with ctrl shift Enter key combination ?


Regards,
DILIPandey

 

[DILIPandey]

Hi AB1023,

You can refer below link to understand Array:-

Array Formulas. Excel Array Formulas


Regards,
DILIPandey

 

[bosco_yip]

I did yes. I made a slight adjustment to the formula and it now seems to be working:

=INDEX('Admin Tracker'!$A$2:$A$700,MAX(IF('Admin Tracker'!$B$2:$B$700=A2,ROW('Admin Tracker'!$B$2:$B$700)-3,-1)))

Why are array formulas different and require the ctrl shift enter strokes?

Please take note that this formula will give wrong results.

Regards

 

[bosco_yip]

Another option, all belows are normal formulas,

1] Month Started (First Session) N2, formula copy down :

=TEXT(1&LEFT(INDEX('Admin Tracker'!A$2:A$1000,MATCH(A2,'Admin Tracker'!B$2:B$1000,0)),3),"mmmm")

2] Month Completed (Month of Last Session) O2, formula copy down :

=TEXT(1&LEFT(INDEX('Admin Tracker'!A$2:A$1000,INDEX(MATCH(2,1/(A2='Admin Tracker'!B$2:B$1000)),)),3),"mmmm")

3] Current Session # Complete P2, formula copy down :

=INDEX('Admin Tracker'!E$2:E$1000,INDEX(MATCH(2,1/(A2='Admin Tracker'!B$2:B$1000)),))

Regards

 

[Aladin Akyurek]

Admin Tracker

Row\Col	A​	B​	E​
1​	Date session occurred	Participant	Session Complete
2​	February 08, 2016 16:00 CST	Charlene Romero	1​
3​	February 08, 2016 16:00 CST	Ashley Clark	1​
4​	February 09, 2016 12:00 CST	Autumn Church	1​
5​	February 10, 2016 12:00 CST	Jeffery Logan	1​
6​	February 11, 2016 09:00 CST	Matt Barondick	1​
7​	April 11, 2016 15:00 CST	Jill Deen	1​
8​	February 17, 2016 13:00 CST	Marla Kouche	1​
9​	February 08, 2016 16:00 CST	Ashley Clark	2​
10​	February 20, 2016 16:00 CST	Ashley Clark	3​
11​	March 09, 2016 12:00 CST	Autumn Church	2​
12​	February 10, 2016 12:00 CST	Jeffery Logan	3​
13​	February 11, 2016 15:00 CST	Matt Barondick	1​
14​	September 17, 2016 13:00 CST	Marla Kouche	6​

Sheet1

Row\Col	A​	B​	E​
1​	Date session occurred	Participant	Session Complete
2​	February 08, 2016 16:00 CST	Charlene Romero	1​
3​	February 08, 2016 16:00 CST	Ashley Clark	1​
4​	February 09, 2016 12:00 CST	Autumn Church	1​
5​	February 10, 2016 12:00 CST	Jeffery Logan	1​
6​	February 11, 2016 09:00 CST	Matt Barondick	1​
7​	April 11, 2016 15:00 CST	Jill Deen	1​
8​	February 17, 2016 13:00 CST	Marla Kouche	1​
9​	February 08, 2016 16:00 CST	Ashley Clark	2​
10​	February 20, 2016 16:00 CST	Ashley Clark	3​
11​	March 09, 2016 12:00 CST	Autumn Church	2​
12​	February 10, 2016 12:00 CST	Jeffery Logan	3​
13​	February 11, 2016 15:00 CST	Matt Barondick	1​
14​	September 17, 2016 13:00 CST	Marla Kouche	6​

In N2 enter:

=LOOKUP(9.99999999999999E+307,SEARCH("|"&months,
    "|"&INDEX('Admin Tracker'!$A$2:$A$14,MATCH(A2,'Admin Tracker'!$B$2:$B$14,0))),months)<strike></strike>

In O2 enter:

=LOOKUP(9.99999999999999E+307,SEARCH("|"&months,"|"&LOOKUP(9.99999999999999E+307,
    1/('Admin Tracker'!$B$2:$B$14=A2),'Admin Tracker'!$A$2:$A$14)),months)<strike></strike>

In P2 enter:

=LOOKUP(9.99999999999999E+307,1/('Admin Tracker'!$B$2:$B$14=$A2),'Admin Tracker'!$E$2:$E$14)<strike></strike>

Note that months is defined in Formulas | Name Manager as referring to:

={"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"}