Looking for a spilled formula returning the value in a table matching 3 conditions

[ED38]

Hello,

I am trying to apply the Map function I found out recently in the below describe situation but it does not work..
if someone could help me. Thank you

here is my formula in A5 (part of the array D5, in column E are the product code list and in F the quantity) :
=MAP(OFFSET(D5#;0;1;;1);Cost_Database[ProductCode];Cost_Database[MinQuantity];Cost_Database[MaxQuantity];Cost_Database[Tier];OFFSET(D5#;0;2;;1);LAMBDA(a;b;c;d;e;f;IF((a=b)*(c>=f)*(d<=f);e;"")))

I would like for each product code in my list get the name of the tier for which the qty matches the range of the tier.
I expect it is clear enough...
Let me know your proposal and potential explanation why my formula does not work

Thank you

 

[StephenCrump]

Your LAMBDA tests a=b, c>=f and d<=f.

Here's an illustration of a=b

Book1.xlsm
	A	B	C
1	a	b	a=b
2	1	2	FALSE
3	2	1	FALSE
4	3	3	TRUE
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=a=b
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
a	=Sheet1!$A$2:$A$4	C2
b	=Sheet1!$B$2:$B$4	C2

It requires a and b to be the same size (I suspect yours will be different sizes) and the comparison is element by element, hence we don't get a match for a =1 or 2, even though both values are in b.

It's hard to be sure without seeing your layout, but I think this is one way that should work for you?

=BYROW(D5#;LAMBDA(r;XLOOKUP(1;(INDEX(r;2)=Cost_Database[ProductCode])*(INDEX(r;3)>=Cost_Database[MinQuantity])*(INDEX(r;3)<=Cost_Database[MaxQuantity]);Cost_Database[Tier];"n/a")))

 

[ED38]

Thanks a lot Stephen.
Your formula works very well (as I expected!)
and thank you for the explanation on top.

 

[ED38]

Hello StephenCrump,
In order to re-use your formula in similar context, I need to understand the usage of INDEX here as that function is supposed to work that way : INDEX(array, row,[column]).
With the 'BYROW' function ahead, I understand INDEX(r, 2) taht way : the 'r' = the value of the row in the 2nd column of the D5# array (the column matching the product code) BUT the '2' is placed at the position supposed to be for the Row#, not the column# so I am confused by this (or I maybe totally misunderstood the overall formula!)
Could you explain how it works please?
Thanks again

 

[StephenCrump]

When the array is one dimensional, you need to specify only the "row" argument.

But in this case, it's really the nth element, rather than the nth row:

	A	B	C	D	E	F
1	A		A	B	C	D
2	B
3	C		3
4	D		C	C
5			C	C
Sheet1

Cell Formulas
Range	Formula
C4	C4	=INDEX(C1:F1,C3)
D4	D4	=INDEX(C1:F1,1,C3)
C5	C5	=INDEX(A1:A4,C3)
D5	D5	=INDEX(A1:A4,C3,1)

If you like, you can specify the "1" for the missing dimension. It's just not necessary to do so.

 

[ED38]

Thank you StephenCrump for the add'l explanation, that's clear!
Have a nice day