Index Match Formula Needed

[Russell1745]

Hi, I'm trying to create an Index match Array formula to return the "Price" (Column H) based on matching the criteria in Column B, Column C and within the ranges of Columns D/E and F/F.
I've tried

, but this does not work. Any help would be appreciated. Thanks

New Calculator.xlsx
	B	C	D	E	F	G	H
336	Grade	Color-1	Gauge	Gauge	Qty	Qty	Price
337	BFVCHXX	035	0.00600	0.00690	0	1999	1.000
338	BFVCHXX	035	0.00600	0.00690	2000	4999	1.100
339	BFVCHXX	035	0.00600	0.00690	5000	9999	1.200
340	BFVCHXX	035	0.00600	0.00690	10000	40000	1.300
341	BFVCHXX	035	0.00700	0.00900	0	1999	1.120
342	BFVCHXX	035	0.00700	0.00900	2000	4999	1.190
343	BFVCHXX	035	0.00700	0.00900	5000	9999	1.000
344	BFVCHXX	035	0.00700	0.00900	10000	40000	1.170
345	BFVCHXX	035	0.00910	0.01100	0	1999	0.990
346	BFVCHXX	035	0.00910	0.01100	2000	4999	1.010
347	BFVCHXX	035	0.00910	0.01100	5000	9999	1.020
348	BFVCHXX	035	0.00910	0.01100	10000	40000	1.030
349	BFVCHXX	035	0.01110	0.01300	0	1999	0.660
350	BFVCHXX	035	0.01110	0.01300	2000	4999	1.000
351	BFVCHXX	035	0.01110	0.01300	5000	9999	1.250
352	BFVCHXX	035	0.01110	0.01300	10000	40000	1.400
Price Matrix

 

[Russell1745]

Microsoft 365

 

[Fluff]

Can you repost the data including the criteria cells in col J & the expected result.

 

[Russell1745]

Sorry...

Grade	Color-1	Gauge	Gauge	Qty	Qty	Price
BFVCHXX	035	0.00600	0.00690	0	1999	1.000
BFVCHXX	035	0.00600	0.00690	2000	4999	1.100
BFVCHXX	035	0.00600	0.00690	5000	9999	1.200
BFVCHXX	035	0.00600	0.00690	10000	40000	1.300
BFVCHXX	035	0.00700	0.00900	0	1999	1.120	Price	#N/A
BFVCHXX	035	0.00700	0.00900	2000	4999	1.190	Gauge	0.0062
BFVCHXX	035	0.00700	0.00900	5000	9999	1.000	Weight	5200
BFVCHXX	035	0.00700	0.00900	10000	40000	1.170	Color	035
BFVCHXX	035	0.00910	0.01100	0	1999	0.990	Grade	BFVCHXX
BFVCHXX	035	0.00910	0.01100	2000	4999	1.010
BFVCHXX	035	0.00910	0.01100	5000	9999	1.020
BFVCHXX	035	0.00910	0.01100	10000	40000	1.030
BFVCHXX	035	0.01110	0.01300	0	1999	0.660
BFVCHXX	035	0.01110	0.01300	2000	4999	1.000
BFVCHXX	035	0.01110	0.01300	5000	9999	1.250
BFVCHXX	035	0.01110	0.01300	10000	40000	1.400
BFVCHXX	035	0.01310	0.01700

 

[Russell1745]

B	C	D	E	F	G	H	I	J
Grade	Color-1	Gauge	Gauge	Qty	Qty	Price
BFVCHXX	035	0.00600	0.00690	0	1999	1.000
BFVCHXX	035	0.00600	0.00690	2000	4999	1.100
BFVCHXX	035	0.00600	0.00690	5000	9999	1.200
BFVCHXX	035	0.00600	0.00690	10000	40000	1.300
BFVCHXX	035	0.00700	0.00900	0	1999	1.120	Price	#N/A
BFVCHXX	035	0.00700	0.00900	2000	4999	1.190	Gauge	0.0062
BFVCHXX	035	0.00700	0.00900	5000	9999	1.000	Weight	5200
BFVCHXX	035	0.00700	0.00900	10000	40000	1.170	Color	035
BFVCHXX	035	0.00910	0.01100	0	1999	0.990	Grade	BFVCHXX
BFVCHXX	035	0.00910	0.01100	2000	4999	1.010
BFVCHXX	035	0.00910	0.01100	5000	9999	1.020
BFVCHXX	035	0.00910	0.01100	10000	40000	1.030
BFVCHXX	035	0.01110	0.01300	0	1999	0.660
BFVCHXX	035	0.01110	0.01300	2000	4999	1.000
BFVCHXX	035	0.01110	0.01300	5000	9999	1.250
BFVCHXX	035	0.01110	0.01300	10000	40000	1.400

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	B	C	D	E	F	G	H	I	J
1	Grade	Color-1	Gauge	Gauge	Qty	Qty	Price
2	BFVCHXX	35	0.006	0.0069	0	1999	1
3	BFVCHXX	35	0.006	0.0069	2000	4999	1.1
4	BFVCHXX	35	0.006	0.0069	5000	9999	1.2
5	BFVCHXX	35	0.006	0.0069	10000	40000	1.3
6	BFVCHXX	35	0.007	0.009	0	1999	1.12	Price	1.2
7	BFVCHXX	35	0.007	0.009	2000	4999	1.19	Gauge	0.0062
8	BFVCHXX	35	0.007	0.009	5000	9999	1	Weight	5200
9	BFVCHXX	35	0.007	0.009	10000	40000	1.17	Color	35
10	BFVCHXX	35	0.0091	0.011	0	1999	0.99	Grade	BFVCHXX
11	BFVCHXX	35	0.0091	0.011	2000	4999	1.01
12	BFVCHXX	35	0.0091	0.011	5000	9999	1.02
13	BFVCHXX	35	0.0091	0.011	10000	40000	1.03
14	BFVCHXX	35	0.0111	0.013	0	1999	0.66
15	BFVCHXX	35	0.0111	0.013	2000	4999	1
16	BFVCHXX	35	0.0111	0.013	5000	9999	1.25
17	BFVCHXX	35	0.0111	0.013	10000	40000	1.4
Master

Cell Formulas
Range	Formula
J6	J6	=FILTER(H2:H17,(B2:B17=J10)*(C2:C17=J9)*(J7>=D2:D17)*(J7<=E2:E17)*(J8>=F2:F17)*(J8<=G2:G17))

 

[Russell1745]

Thanks for that.
How about

Fluff.xlsm
	B	C	D	E	F	G	H	I	J
1	Grade	Color-1	Gauge	Gauge	Qty	Qty	Price
2	BFVCHXX	35	0.006	0.0069	0	1999	1
3	BFVCHXX	35	0.006	0.0069	2000	4999	1.1
4	BFVCHXX	35	0.006	0.0069	5000	9999	1.2
5	BFVCHXX	35	0.006	0.0069	10000	40000	1.3
6	BFVCHXX	35	0.007	0.009	0	1999	1.12	Price	1.2
7	BFVCHXX	35	0.007	0.009	2000	4999	1.19	Gauge	0.0062
8	BFVCHXX	35	0.007	0.009	5000	9999	1	Weight	5200
9	BFVCHXX	35	0.007	0.009	10000	40000	1.17	Color	35
10	BFVCHXX	35	0.0091	0.011	0	1999	0.99	Grade	BFVCHXX
11	BFVCHXX	35	0.0091	0.011	2000	4999	1.01
12	BFVCHXX	35	0.0091	0.011	5000	9999	1.02
13	BFVCHXX	35	0.0091	0.011	10000	40000	1.03
14	BFVCHXX	35	0.0111	0.013	0	1999	0.66
15	BFVCHXX	35	0.0111	0.013	2000	4999	1
16	BFVCHXX	35	0.0111	0.013	5000	9999	1.25
17	BFVCHXX	35	0.0111	0.013	10000	40000	1.4
Master

Cell Formulas
Range	Formula
J6	J6	=FILTER(H2:H17,(B2:B17=J10)*(C2:C17=J9)*(J7>=D2:D17)*(J7<=E2:E17)*(J8>=F2:F17)*(J8<=G2:G17))

Thanks for your effort but did not work,

 

[Fluff]

In what way?

 

[Russell1745]

It returns the result "1" which is the value in H2

 

[Fluff]

Can you post the data with the formula using the mini-sheet option, as you can see from post#6 it's returns the correct value for me.