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

A

AB1023

New Member
Joined
Feb 20, 2014
Messages
3
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):

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[/TR]
[TR]
[TD="align: center"]Participant name [/TD]
[TD][TABLE="width: 432"]
<tbody>[TR]
[TD="align: center"]Month Started (First Session)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]Month Completed (Month of Last Session)[/TD]
[TD="align: center"]Current Session # Complete[/TD]
[/TR]
[TR]
[TD]Marla Kouche
[/TD]
[TD="align: center"]February[/TD]
[TD="align: center"]September[/TD]
[TD="align: center"]6
[/TD]
[/TR]
</tbody>[/TABLE]


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

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


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 :)
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Hi AB1023,

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


Regards,
DILIPandey
 
Upvote 0
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
 
Upvote 0
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!



DILIPandey said:
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
Click to expand...
 
Upvote 0
I got the correct results.

have you entered formula with ctrl shift Enter key combination ?


Regards,
DILIPandey
 
Upvote 0
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?



DILIPandey said:
I got the correct results.

have you entered formula with ctrl shift Enter key combination ?


Regards,
DILIPandey
Click to expand...
 
Upvote 0
AB1023 said:
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?
Click to expand...

Please take note that this formula will give wrong results.

Regards
 
Upvote 0
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
 
Upvote 0
Admin Tracker

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td]Date session occurred[/td][td]Participant[/td][td]Session Complete[/td][/tr]


[tr][td]
2​
[/td][td]February 08, 2016 16:00 CST[/td][td]Charlene Romero[/td][td]
1​
[/td][/tr]


[tr][td]
3​
[/td][td]February 08, 2016 16:00 CST[/td][td]Ashley Clark[/td][td]
1​
[/td][/tr]


[tr][td]
4​
[/td][td]February 09, 2016 12:00 CST[/td][td]Autumn Church[/td][td]
1​
[/td][/tr]


[tr][td]
5​
[/td][td]February 10, 2016 12:00 CST[/td][td]Jeffery Logan[/td][td]
1​
[/td][/tr]


[tr][td]
6​
[/td][td]February 11, 2016 09:00 CST[/td][td]Matt Barondick[/td][td]
1​
[/td][/tr]


[tr][td]
7​
[/td][td]April 11, 2016 15:00 CST[/td][td]Jill Deen[/td][td]
1​
[/td][/tr]


[tr][td]
8​
[/td][td]February 17, 2016 13:00 CST[/td][td]Marla Kouche[/td][td]
1​
[/td][/tr]


[tr][td]
9​
[/td][td]February 08, 2016 16:00 CST[/td][td]Ashley Clark[/td][td]
2​
[/td][/tr]


[tr][td]
10​
[/td][td]February 20, 2016 16:00 CST[/td][td]Ashley Clark[/td][td]
3​
[/td][/tr]


[tr][td]
11​
[/td][td]March 09, 2016 12:00 CST[/td][td]Autumn Church[/td][td]
2​
[/td][/tr]


[tr][td]
12​
[/td][td]February 10, 2016 12:00 CST[/td][td]Jeffery Logan[/td][td]
3​
[/td][/tr]


[tr][td]
13​
[/td][td]February 11, 2016 15:00 CST[/td][td]Matt Barondick[/td][td]
1​
[/td][/tr]


[tr][td]
14​
[/td][td]September 17, 2016 13:00 CST[/td][td]Marla Kouche[/td][td]
6​
[/td][/tr]
[/table]

Sheet1

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
E​
[/td][/tr]
[tr][td]
1​
[/td][td]Date session occurred[/td][td]Participant[/td][td]Session Complete[/td][/tr]


[tr][td]
2​
[/td][td]February 08, 2016 16:00 CST[/td][td]Charlene Romero[/td][td]
1​
[/td][/tr]


[tr][td]
3​
[/td][td]February 08, 2016 16:00 CST[/td][td]Ashley Clark[/td][td]
1​
[/td][/tr]


[tr][td]
4​
[/td][td]February 09, 2016 12:00 CST[/td][td]Autumn Church[/td][td]
1​
[/td][/tr]


[tr][td]
5​
[/td][td]February 10, 2016 12:00 CST[/td][td]Jeffery Logan[/td][td]
1​
[/td][/tr]


[tr][td]
6​
[/td][td]February 11, 2016 09:00 CST[/td][td]Matt Barondick[/td][td]
1​
[/td][/tr]


[tr][td]
7​
[/td][td]April 11, 2016 15:00 CST[/td][td]Jill Deen[/td][td]
1​
[/td][/tr]


[tr][td]
8​
[/td][td]February 17, 2016 13:00 CST[/td][td]Marla Kouche[/td][td]
1​
[/td][/tr]


[tr][td]
9​
[/td][td]February 08, 2016 16:00 CST[/td][td]Ashley Clark[/td][td]
2​
[/td][/tr]


[tr][td]
10​
[/td][td]February 20, 2016 16:00 CST[/td][td]Ashley Clark[/td][td]
3​
[/td][/tr]


[tr][td]
11​
[/td][td]March 09, 2016 12:00 CST[/td][td]Autumn Church[/td][td]
2​
[/td][/tr]


[tr][td]
12​
[/td][td]February 10, 2016 12:00 CST[/td][td]Jeffery Logan[/td][td]
3​
[/td][/tr]


[tr][td]
13​
[/td][td]February 11, 2016 15:00 CST[/td][td]Matt Barondick[/td][td]
1​
[/td][/tr]


[tr][td]
14​
[/td][td]September 17, 2016 13:00 CST[/td][td]Marla Kouche[/td][td]
6​
[/td][/tr]
[/table]

In N2 enter:
Rich (BB code): 
=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:
Rich (BB code): 
=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:
Rich (BB code): 
=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:
Rich (BB code): 
={"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"}
 
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
536
skyh3ck
skyh3ck
C
  • Question Question
Find first match from one range in another range and return related value
Replies
8
Views
1K
Fluff
Fluff
Z
  • Locked
  • Question Question
Excel formula to return column header from first cell from consecutive cells below a threshold
Replies
1
Views
1K
Joe4
Joe4
A
  • Question Question
Return a text value that is not equal to a text value with a lookup or array
Replies
4
Views
737
Allkair
A
M
  • Question Question
Challenging: VBA to Combine (1) Naming Ranges with Another Range's Values; (2) Row & Column Loops; (Finding Last Row/Column)
Replies
1
Views
596
Leith Ross
Leith Ross

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

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
Back
Top