How does one get around Xlookups duplicates vs filters size limitations?

[BEDE]

Hello,

Can't figure this out. I have two data sets I'm trying to match up. The issue is criteria in one data set is limited to dates and numbers, both can be matched, however the return array has a (encore) on certain days. For example, with xlookup the return array contains duplicates on days that have encore, the return array doesn't account that for ThisMovie vs ThisMovie(Encore) it just puts one based on search order. Filter on the other hand does account for it but the array needs to be of compatible size, which is probably how it returns correct data because it just does it verbatim every row idk

SAME RESULTS
=XLOOKUP(DATEVALUE(DATASET[dates])*DATASET[numbers],DATEVALUE('INFO (5)'!F12#)*'INFO (5)'!B12#,'INFO(5)'!A12#)
=INDEX('INFO(5)'!A12#,XMATCH(DATEVALUE(DATASET[dates])*DATASET[numbers],DATEVALUE('INFO(5)'!F12#)*'INFO(5)'!B12#,0,1))

Also why does boolean logic not work if the starting dates are different? does it too need to be of a compatible sizes?
--(DATEVALUE(DATASET[dates])=DATEVALUE('INFO (5)'!F12#))

 

[MountainFog]

Can you provide a small sample dummy data set?

 

[BEDE]

Here's an example, filter works but it needs to be same size which it will never be.

production.xlsb
	O	P	Q	R	S	T	U	V	W
10	CR1	CR2		CR1	CR2	Return	Xlookupup	Xlookupup reverse	filter
11	4/8/2024	250		4/8/2024	250	name date a250	name date a250	name date a250	name date a250
12	4/8/2024	500		4/8/2024	500	name date a500	name date a500	name date a500	name date a500
13	4/8/2024	750		4/8/2024	750	name date a750	name date a750	name date a750	name date a750
14	4/9/2024	250		4/9/2024	250	name date a250	name date a250	name date a250	name date a250
15	4/9/2024	500		4/9/2024	500	name date a500	name date a500	name date a500	name date a500
16	4/9/2024	750		4/9/2024	750	name date a750	name date a750	name date a750	name date a750
17	4/10/2024	250		4/10/2024	250	name(encore) date a250	name(encore) date a250	name date a250	name(encore) date a250
18	4/10/2024	500		4/10/2024	500	name(encore) date a500	name(encore) date a500	name date a500	name(encore) date a500
19	4/10/2024	750		4/10/2024	750	name(encore) date a750	name(encore) date a750	name date a750	name(encore) date a750
20	4/10/2024	250		4/10/2024	250	name date a250	name(encore) date a250	name date a250	name date a250
21	4/10/2024	500		4/10/2024	500	name date a500	name(encore) date a500	name date a500	name date a500
22	4/10/2024	750		4/10/2024	750	name date a750	name(encore) date a750	name date a750	name date a750
23	4/11/2024	250		4/11/2024	250	name date a250	name date a250	name date a250	name date a250
24	4/11/2024	500		4/11/2024	500	name date a500	name date a500	name date a500	name date a500
25	4/11/2024	750		4/11/2024	750	name date a750	name date a750	name date a750	name date a750
Sheet1

Cell Formulas
Range	Formula
U11:U25	U11	=XLOOKUP(O11:O25*P11:P25,R11:R25*S11:S25,T11:T25)
V11:V25	V11	=XLOOKUP(O11:O25*P11:P25,R11:R25*S11:S25,T11:T25,,,-1)
W11:W25	W11	=FILTER(T11:T25,O11:O25*P11:P25=R11:R25*S11:S25)
Dynamic array formulas.

 

[MountainFog]

I'm not really sure what you are trying to do? Are columns O:P the data that you are looking up, R:T the columns that you are trying to match against?

Given a date and CR2, how to you know if the correct one was pulled? what tells you if it was an encore or not?

 

[BEDE]

SCAN(0,O11:S25,LAMBDA(AA,BB,CC,DD,EE,XLOOKUP(AA,CC,EE,"EMPTY",0))) <- FAILED.

 

[BEDE]

I'm not really sure what you are trying to do? Are columns O:P the data that you are looking up, R:T the columns that you are trying to match against?

Given a date and CR2, how to you know if the correct one was pulled? what tells you if it was an encore or not?

"CR1'= Criteria Range 1 etc, so basically, dataone(Criteria Range 1, Criteria Range 2) datatwo(cr1, cr2, return array)