Index(Match(Max(If - with multiple conditions

[Frysk]

Hello, and thank you for looking.
I've finally reached my limit with this one, and created an account to seek help.

Desired Function in Sheet1(C3) to result in a name from Sheet2(column B).

Conditions:
1. The unique identifiers must match (identifiers will be listed only once each on Sheet1, but many duplicates on Sheet2)
2. The date in Sheet2(column C) must be less than (earlier) or equal to the date in Sheet1(C$1)
3. The date in Sheet2(column C) must be the maximum possible date that allows conditions 1 and 2 to be true

There are thousands of rows in Sheet2. I have the maximum row number calculated as an integer at cell Sheet2!($Z$2), and am using "Indirect("Sheet2!$A$1:$A$"&Sheet2!$Z$2) to calculate the range accurately and allow Macros to automatically fill down such things as the Desired Function, without filling beyond the row limits.

Here is the closest I've got, with help from this site (thank you):

=INDEX(INDIRECT("Sheet2!$B$1:$B$"&Sheet2!$Z$2),MATCH(MAX(IF(AND(INDIRECT("Sheet2!$A$1:$A$"&Sheet2!$Z$2)=Sheet1!$A2,INDIRECT("Sheet2!$C$1:$C$"&Sheet2!$Z$2)<=Sheet1!C$1),INDIRECT("Sheet2!$C$1:$C$"&Sheet2!$Z$2))),INDIRECT("Sheet2!$C$1:$C$"&Sheet2!$Z$2),0)))

Finally, just in case it's relevant, I've simplified the columns for this exercise.
On the actual sheets there are many columns in between, some empty, some filled with data. Columns C-E on Sheet1 will be next to one another, however.

Table 1 (Sheet1)

A	B	C	D	E
		30/09/2022	31/8/2022	31/7/2022
uniqueidentifierA	data_ignore	Desired Function	fill function	fill function
uniqueidentifierB	data_ignore	fill function	fill function	fill function

Table 2 (Sheet2)

A	B	C
uniqueidentifier	Name 1	date
uniqueidentifier	Name 2	date

Thank you again for looking.
Regards,
Frysk

 

[Automatrix]

Are you able to sort the data in Table 2 by date (newest to oldest)?

 

[Frysk]

Table 2 pulls through in a pivot table from a data source. I could replicate the entire table, to enable sorting, but this WorkBook is already monstrous and I'm attempting to constraint the work required.

 

[Automatrix]

It sounds like something that would be best solved in Power Query. But it's hard to tell without more details.

 

[Frysk]

I stumbled my way through to the end on this.

Also removed instances of "INDIRECT" having learned that it's a volatile function and was hugely slowing my workbook.
Here is the solution, to replace "Desired Function" in Sheet1 cell C2:

=MATCH(1,(Sheet2!$C$1:$C$99999=MAXIFS(Sheet2!$C$1:$C$99999,Sheet2!$C$1:$C$99999,"<="&C$1,Sheet2!$A$1:$A$99999,"="&$A2))*(Sheet2!$A$1:$A$99999=$A2),0)))

 

[Peter_SSs]

I have removed the 'Solution mark' from post #5 since that was an invalid formula. I think that you only posted part of your working formula?
That will save future readers from being misled by that part-formula. If you prefer your own formula over the one below then you are welcome to post the whole formula and mark that solution.

Here's an alternative that you could consider. I think it does what you want.

Frysk.xlsm
	A	B	C
1	uniqueidentifierA	Name 1	1/09/2022
2	uniqueidentifierB	Name 2	12/10/2022
3	uniqueidentifierA	Name 3	3/12/2022
4	uniqueidentifierB	Name 4	23/03/2023
5	uniqueidentifierA	Name 5	22/08/2022
6	uniqueidentifierB	Name 6	5/07/2022
7	uniqueidentifierA	Name 7	23/04/2023
8	uniqueidentifierB	Name 8	17/08/2022
9	uniqueidentifierA	Name 9	5/02/2023
10	uniqueidentifierB	Name 10	21/03/2023
11	uniqueidentifierA	Name 11	18/09/2022
12	uniqueidentifierB	Name 12	22/10/2022
13	uniqueidentifierA	Name 13	27/12/2022
14	uniqueidentifierB	Name 14	20/09/2022
15	uniqueidentifierA	Name 15	30/09/2022
16	uniqueidentifierB	Name 16	5/05/2023
17	uniqueidentifierA	Name 17	6/05/2023
18	uniqueidentifierB	Name 18	3/06/2023
19	uniqueidentifierA	Name 19	15/09/2022
20	uniqueidentifierB	Name 20	12/09/2022
Sheet2

Frysk.xlsm
	A	B	C	D	E
1			30/09/2022	31/08/2022	31/07/2022
2	uniqueidentifierA	data_ignore	Name 15	Name 5
3	uniqueidentifierB	data_ignore	Name 14	Name 8	Name 6
Sheet1

Cell Formulas
Range	Formula
C2:E3	C2	=IFERROR(INDEX(SORT(FILTER(Sheet2!$B$1:$C$99999,(Sheet2!$A$1:$A$99999=$A2)*(Sheet2!$C$1:$C$99999<=C$1)),2,-1),1,1),"")

 

[Frysk]

Thank you for that. Yes, I mistakenly left some off the solution when converting it from my actual random rows/columns.
I'll mark yours as solution - thank you again - but here is mine for anyone who is unable (due to versions) to use SORT or FILTER:

=IFERROR(INDEX(Sheet2!$B$1:$B$9999, MATCH(1, (Sheet2!$C$1:$C$9999=MAXIFS(Sheet2!$C$1:$C$9999,Sheet2!$C$1:$C$9999,"<="&Sheet1!C$1,Sheet2!$A$1:$A$9999,"="&Sheet1!$A2))*(Sheet2!$A$1:$A$9999=Sheet1!$A2),0)), "")

 

[Peter_SSs]

Cheers.

Something to keep in mind though is that it is a bad idea to use the sheet name of the sheet that the formula is on in a formula. There are circumstances where that can lead to incorrect results.
Example: Create new sheet, name it, and add a formula with for loop?

So, your formula would be better (& a little shorter) like this
=IFERROR(INDEX(Sheet2!$B$1:$B$9999, MATCH(1, (Sheet2!$C$1:$C$9999=MAXIFS(Sheet2!$C$1:$C$9999,Sheet2!$C$1:$C$9999,"<="&Sheet1!C$1,Sheet2!$A$1:$A$9999,"="&Sheet1!$A2))*(Sheet2!$A$1:$A$9999=Sheet1!$A2),0)), "")
=IFERROR(INDEX(Sheet2!$B$1:$B$9999, MATCH(1, (Sheet2!$C$1:$C$9999=MAXIFS(Sheet2!$C$1:$C$9999,Sheet2!$C$1:$C$9999,"<="&C$1,Sheet2!$A$1:$A$9999,"="&Sheet1!$A2))*(Sheet2!$A$1:$A$9999=$A2),0)), "")

 

[Frysk]

Nice tip, cheers.