Intuitively I'd expected to combine FILTER, SORT and UNIQUE but I can't get them to do what I want.

[IfAllElseFails]

I want a SINGLE formula to return a SINGLE value from a table:
1) matching, in one column, a specified item
2) also, matching, in a second column, the highest date which is lower than a specified date

I can only manage it in 2 steps

I'm still getting to grips with functions which return multiple results

Help much appreciated
(sorry can't use the addin to upload a mini sheet - locked down corporate excel install)

 

[felixstraube]

Sure. Sorry about that.

 

[IfAllElseFails]

interestingly the TAKE example didn't work correctly when I tested it with the range of dates below, equal to, and higher than the dates in the table but your one (with choosecols added) did

 

[felixstraube]

Although yours returns the date i think not the text in column C.

 

[IfAllElseFails]

MrExcelPlayground20 (version 1).xlsb
	A	B	C	D	E
1	Item	Date	Guidance
2	45.02	9/23/2023	Thing1	Item	45.02
3	45.02	12/12/2022	Thing2	Date	3/23/2023
4	45.02	1/11/2023	Thing3
5	45.02	7/14/2023	Thing4		Thing3
6	65.01	9/23/2023	Thing5
7	65.01	1/11/2023	Thing6
8	65.01	7/14/2023	Thing7
9	65.01	1/1/2024	Thing8
10	19.12	9/23/2023	Thing9
11	19.12	1/11/2023	Thing10
Sheet24

Cell Formulas
Range	Formula
E5	E5	=INDEX(CHOOSECOLS(SORT(FILTER(B2:C11,A2:A11=E2),1),2),MATCH(E3,CHOOSECOLS(SORT(FILTER(B2:C11,A2:A11=E2),1),1),1))

This doesn't appear to work correctly for the higher dates - but I'm still testing.

 

[dreid1011]

interestingly the TAKE example didn't work correctly when I tested it with the range of dates below, equal to, and higher than the dates in the table but your one (with choosecols added) did

Interesting indeed. I replaced the CHOOSEROWS on my suggestion with TAKE as Fluff was using it, and it seemed to work fine for me, however, I did not yet test with a different range of dates:

=TAKE(SORT(FILTER(B2:C1000,((A2:A1000=J4)*(B2:B1000<=J5))),1,-1),1,-1)

 

[IfAllElseFails]

MrExcelPlayground20 (version 1).xlsb
	A	B	C	D	E
1	Item	Date	Guidance
2	45.02	9/23/2023	Thing1	Item	45.02
3	45.02	12/12/2022	Thing2	Date	3/23/2023
4	45.02	1/11/2023	Thing3
5	45.02	7/14/2023	Thing4		Thing3
6	65.01	9/23/2023	Thing5
7	65.01	1/11/2023	Thing6
8	65.01	7/14/2023	Thing7
9	65.01	1/1/2024	Thing8
10	19.12	9/23/2023	Thing9
11	19.12	1/11/2023	Thing10
Sheet24

Cell Formulas
Range	Formula
E5	E5	=INDEX(CHOOSECOLS(SORT(FILTER(B2:C11,A2:A11=E2),1),2),MATCH(E3,CHOOSECOLS(SORT(FILTER(B2:C11,A2:A11=E2),1),1),1))

Thanks James

 

[Fluff]

interestingly the TAKE example didn't work correctly when I tested it with the range of dates below, equal to, and higher than the dates in the table but your one (with choosecols added) did

There was a typo on mine, it should have been

=TAKE(SORT(FILTER(B2:C1000,(A2:A1000=J4)*(B2:B1000<=J5)),1,-1),1,-1)

 

[IfAllElseFails]

Interesting indeed. I replaced the CHOOSEROWS on my suggestion with TAKE as Fluff was using it, and it seemed to work fine for me, however, I did not yet test with a different range of dates:

=TAKE(SORT(FILTER(B2:C1000,((A2:A1000=J4)*(B2:B1000<=J5))),1,-1),1,-1)

My bad. For some reason I had C2:C1000. When I corrected it to b2:C1000 it worked

 

[IfAllElseFails]

Taking @Fluff 's suggestion into account, not to use whole columns:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Item ID	Date Effective	Guidance
2	45.02	23/09/2023	A						Item ID	45.02
3	45.02	12/12/2023	B						On Date	15/03/2023
4	45.02	10/01/2023	THIS
5	45.02	14/07/2023	D						Result	THIS
6	65.01	23/09/2023	AA
7	65.01	12/12/2023	BB
8	65.01	10/01/2023	CC
9	65.01	14/07/2023	DD
Sheet2

Cell Formulas
Range	Formula
J5	J5	=FILTER(C1:C1000,(A1:A1000=$J$2)*(B1:B1000=MAX(IFERROR(B1:B1000*(B1:B1000<=$J$3)*(A1:A1000=$J$2),0))))

Thanks Felix

 

[IfAllElseFails]

dreid1011, Fluff, felixstraube, JamesCanale​

Thanks so much for your rapid responses and effective solutions.

I am going to spend a bit more time looking in more detail at all the answers (which all seem to work ) so that I can understand how they work!