Xlookup Between Two Values

[acool]

Hi Everyone,
I am currently trying to create an xlookup formula that provides provides a value back based on a Min & Max. In this example, I would like to find the value if the Dimensional weight in F2 falls between the Weight Min & Max, and the Tier Size also matches in Column A. So in this example, the incremental fee in column H would be .40 because Shipping classification is standard and the dimensional weight falls between 4-20. Any help in creating this formula would be greatly appreciated.

Thank You!

 

[DanteAmor]

Try:

varios 12ene2024.xlsm
	A	B	C	D	E	F	G	H
1	Shipping	Min	Max	Incremental Fee		dim Weight	Tier Size	Incremental Fee
2	Standard	0	1	0		5	Standard	0.40
3	Standard	2	2	0
4	Standard	3	3	0
5	Standard	4	20	0.4
6	Standard	21	30	0.4
7	Standard	31	50	0.4
8	Standard	51	150	0.4
9	Overside	0	1	0
10	Overside	2	2	0
11	Overside	3	3	0
12	Overside	4	20	0.4
13	Overside	21	30	0.4
14	Overside	31	50	0.4
15	Overside	51	150	0.4
16	Large-Oversize	0	1	0
17	Large-Oversize	2	2	0
18	Large-Oversize	3	3	0
19	Large-Oversize	4	20	0.4
20	Large-Oversize	21	30	0.4
21	Large-Oversize	31	50	0.4
22	Large-Oversize	51	150	0.4
23	Big	150	500	0.8
Hoja2

Cell Formulas
Range	Formula
H2	H2	=SUMIFS(D2:D23,A2:A23,G2,B2:B23,"<="&F2,C2:C23,">="&F2)

NOTE: I adjusted the values in the first values, because, for example, the number 1 appears in 2 shipping.


----- --

Version for 365:

=FILTER(D2:D23,(A2:A23=G2)*(B2:B23<=F2)*(C2:C23>=F2))