find most recent occurrences

[vicndave]

Given raw data in A1:D4 showing values for certain dates,

​	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Date​	Val1​	Val2​	Val3​	​	All Vals​	latest date​	2nd latest​	3rd latest​
2​	04/02/23​	Aaron​	Barbara​	Evan​	​	Aaron​	04/23/23​	04/16/23​	04/02/23​
3​	04/09/23​	Barbara​	Charlie​	Dorcas​	​	Barbara​	04/23/23​	04/09/23​	04/02/23​
4​	04/16/23​	Frank​	Heather​	Aaron​	​	Charlie​	04/23/23​	04/09/23​	​
5​	04/23/23​	Charlie​	Aaron​	Barbara​	​	Dorcas​	04/09/23​	​	​
6​	​	​	​	​	​	Evan​	04/02/23​	​	​
7​	​	​	​	​	​	Frank​	04/16/23​	​	​
8​	​	​	​	​	​	Greta​	​	​	​
9​	​	​	​	​	​	Heather​	04/16/23​	​	​

What formula(s) can produce the data in F1:I9. showing the 3 most recent dates the value in column F occurred in any of columns B through D?

I can find functions that do parts of what I want, but I'm having trouble combining them into formulas to get the complete behavior.

 

[Tetra201]

No such formula(s): there is no Greta in columns B through D

 

[Peter_SSs]

I suggest that you update your Account details (or 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’)

 

[vicndave]

Thank you for taking a look at this.

I didn't specify version because the file is shared among multiple users. I think all are on Windows, but versions include 365, some unknown versions of Excel, and even LibreOffice 7.2.1.2 (x64) Calc. I'd be happy to get it working in any of these, but we can start with 365.

My mistake, the formulas should be in G2:I9, not F1:I9. Column F is the master value list, and values in columns B-D are taken from column F. The absence of Greta in columns B through D is why there are no dates for Greta in G8:I8.

In 365 I created table 'data' with range A2:D5 and tried a number of things. For example, entering this formula in G2:
=IFERROR(INDEX(data[Date],SMALL(IF(data[Val2]=$F2,ROW(data)-MIN(ROW(data))+1),1)),"")

gives the right result by accident:
4/23/23

because it only looks for 'Aaron' in column C (Val2). If I modify the formula to look for 'Aaron' in B, C or D (Val1, Val2, Val3) as required:
=IFERROR(INDEX(data[Date],SMALL(IF(OR(data[Val1]=$F2,data[Val2]=$F2,data[Val3]=$F2),ROW(data)-MIN(ROW(data))+1),1)),"")

it gives the wrong result:
4/2/23

 

[Tetra201]

Try these steps (tested to work in Excel 2010 and above):
1) Copy the following formula and paste it in cell G2

=IFERROR(AGGREGATE(14,6,data[Date]*SIGN(SEARCH($F2,data[Val1]&"|"&data[Val2]&"|"&data[Val3])),{1,2,3}),"")

2) Select range G2:I2;
3) Press the F2 key -- you should see a blinking cursor at the end of the formula;
4) Press and hold the Ctrl and Shift keys, then press the Enter key -- you should see range G2:I2 populated;
5) Drag-copy range G2:I2 down as needed.

 

[Peter_SSs]

Try these steps (tested to work in Excel 2010 and above):

I would suggest a slight tweak to avoid a possible error like G7 below (I have added to & changed the sample data).
Changed suggestion in bottom section of columns F:I

23 08 07.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	Val1	Val2	Val3		All Vals	latest date	2nd latest	3rd latest
2	2/04/2023	Aaron	Barbara	Evan		Aaron	23/04/2023	16/04/2023	2/04/2023
3	9/04/2023	Barbara	Charlie	Dorcas		Barbara	23/04/2023	9/04/2023	2/04/2023
4	16/04/2023	Frank	Heather	Aaron		Charlie	23/04/2023	9/04/2023
5	23/04/2023	Charlie	Aaron	Barbara		Dorcas	9/04/2023
6	30/04/2023	Heather	Franklin	Evan		Evan	30/04/2023	2/04/2023
7						Frank	30/04/2023	16/04/2023
8						Franklin	30/04/2023
9						Greta
10						Heather	30/04/2023	16/04/2023
11
12						All Vals	latest date	2nd latest	3rd latest
13						Aaron	23/04/2023	16/04/2023	2/04/2023
14						Barbara	23/04/2023	9/04/2023	2/04/2023
15						Charlie	23/04/2023	9/04/2023
16						Dorcas	9/04/2023
17						Evan	30/04/2023	2/04/2023
18						Frank	16/04/2023
19						Franklin	30/04/2023
20						Greta
21						Heather	30/04/2023	16/04/2023
Last 3

Cell Formulas
Range	Formula
G2:I10	G2	=IFERROR(AGGREGATE(14,6,data3[Date]*SIGN(SEARCH($F2,data3[Val1]&"|"&data3[Val2]&"|"&data3[Val3])),{1,2,3}),"")
G13:I21	G13	=IFERROR(AGGREGATE(14,6,data3[Date]*SIGN(SEARCH("|"&$F13&"|","|"&data3[Val1]&"|"&data3[Val2]&"|"&data3[Val3]&"|")),{1,2,3}),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tetra201]

A very good point -- thank you, Peter.

 

[vicndave]

Who would have thought I'd encounter such beautiful people on the internet today? Thank you both!
I wish I could have marked both as the solution.

 

[Peter_SSs]

You are very welcome. Glad we could help. Thanks for the follow-up.