Index/Matchfrom Right to Left?

[carlidee]

Hello,

I created a formula to look up the most recent event name and date in a tracker. Was working great in examples 1 and 2 below, but ran into an issue when two events have the same greatest date.

In example 3 below I would want the latest date to say "Interview 2" on "2/1/24" (rather than Interview 1)

Is there a way to start the look up from right to left - or any other ideas to rework formula below?

Thank you!

	calc output (col A)	calc output (col B)	input (col C)	input (col D)	input (col E)	input (col F)	input (col G)	input (col H)
header	Latest Event Name	Latest Event Date	Event 1 Name	Event 1 Date	Event 2 Name	Event 2 Date	Event 3 Name	Event 3 Date
example 1	Interview 2	1/15/24	Interview 1	1/1/24	Interview 2	1/15/24
example 2	Interview 1	1/10/24	Interview 1	1/10/24
example 3	Interview 1	2/1/24	Interview 1	2/1/24	Interview 2	2/1/24
example 3 formulas	=INDEX(C4:H4,MATCH(B4,C4:H4,0)+1)	=MAX(B4:H4)

 

[Fluff]

How about

=LOOKUP(2,1/(ISNUMBER(D2:H2)),C2:G2)

 

[carlidee]

How about

=LOOKUP(2,1/(ISNUMBER(D2:H2)),C2:G2)

ugh I realized I mis-ordered the columns in the example...should be date then name....

Can you please help fit formula to this structure - also is the formula you recommended for Column B and I would keep Column A as is? Thank you

	calc output (col A)	calc output (col B)	input (col C)	input (col D)	input (col E)	input (col F)	input (col G)	input (col H)
header	Latest Event Date	Latest Event Name	Event 1 Date	Event 1 Name	Event 2 Date	Event 2 Name	Event 3 Name	Event 3 Date
example 1	1/15/24	Interview 2	1/1/24	Interview 1	1/15/24	Interview 2
example 2	1/10/24	Interview 1	1/10/24	Interview 1
example 3	2/1/24	Interview 1	2/1/24	Interview 1	2/1/24	Interview 2
example 3 formulas	=MAX(B4:H4)	=INDEX(C4:H4,MATCH(B4,C4:H4,0)+1)

 

[Fluff]

Ok, try

=LOOKUP(2,1/(ISNUMBER(C2:G2)),D2:H2)

 

[carlidee]

Thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback.