INDEX MATCH

[j4ymf]

Good Evening

Im looking to create an index match but its not working unless i type the exact match, can you match a =>
if you look at my second table, how would i return the yellow query prices when the match is not in the lookup table?


Thank you in advance.

FS Rate Builder v8.xlsm
	A	B	C
1	Intumescent Batt and Mastic	0.025	18.90
2	Intumescent Batt and Mastic	0.05	27.70
3	Intumescent Batt and Mastic	0.1	41.00
4	Intumescent Batt and Mastic	0.2	55.10
5	Intumescent Batt and Mastic	0.3	68.40
6	Intumescent Batt and Mastic	0.4	78.40
7	Intumescent Batt and Mastic	0.5	90.40
8	Intumescent Batt and Mastic	0.6	93.30
9	Intumescent Batt and Mastic	0.7	106.00
10	Intumescent Batt and Mastic	0.8	114.40
11	Intumescent Batt and Mastic	0.9	122.80
12	Intumescent Batt and Mastic	1	135.70
13
14	Wall Head - Linear Batt and Mastic	100	23.00
15	Wall Head - Linear Batt and Mastic	200	30.00
16	Wall Head - Linear Batt and Mastic	400	47.00
17
18	Trapezoidal Infills - Linear Meter	50	35.00
19	Trapezoidal Infills - Linear Meter	100	45.00
20	Trapezoidal Infills - Linear Meter	200	61.00
21
22	Wrap or High Expansion Mastic	55	20.00
23	Wrap or High Expansion Mastic	109	20.00
24	Wrap or High Expansion Mastic	110	26.00
25	Wrap or High Expansion Mastic	159	26.00
26	Wrap or High Expansion Mastic	160	33.00
27	Wrap or High Expansion Mastic	300	33.00
28
29	Horizontal / Vertical Joints - Linear Mastic Seal	30	7.80
30	Horizontal / Vertical Joints - Linear Mastic Seal	100	7.80
31
32	Collars - Mulcol	55	0.00
33	Collars - Mulcol	109	0.00
34	Collars - Mulcol	110	0.00
35	Collars - Mulcol	159	0.00
36	Collars - Mulcol	160	0.00
37	Collars - Mulcol	300	0.00
38
39	Collars - Protecta / Rockwool	55	23.00
40	Collars - Protecta / Rockwool	109	23.00
41	Collars - Protecta / Rockwool	110	28.00
42	Collars - Protecta / Rockwool	159	28.00
43	Collars - Protecta / Rockwool	160	35.00
44	Collars - Protecta / Rockwool	300	35.00
45
46	Mastic Only Penetration	0.025	18.90
47	Mastic Only Penetration	0.05	27.70
48	Mastic Only Penetration	0.1	41.00
49	Mastic Only Penetration	0.2	55.10
50	Mastic Only Penetration	0.3	68.40
51	Mastic Only Penetration	0.4	78.40
52	Mastic Only Penetration	0.5	90.40
53	Mastic Only Penetration	0.6	93.30
54	Mastic Only Penetration	0.7	106.00
55	Mastic Only Penetration	0.8	114.40
56	Mastic Only Penetration	0.9	122.80
57	Mastic Only Penetration	1	135.70
Sell Rates (2)

Cell Formulas
Range	Formula
C1:C12	C1	='% Format'!I9
C14:C16,C25:C26	C14	='% Format'!I24
C18:C20,C23:C24	C18	='% Format'!I29
C22	C22	='% Format'!I34
C27	C27	='% Format'!I36
C29	C29	='% Format'!I23
C30	C30	='% Format'!I23
C39	C39	='% Format'!I39
C40:C41	C40	='% Format'!I39
C42:C43	C42	='% Format'!I40
C44	C44	='% Format'!I41
C46:C57	C46	='% Format'!I9

FS Rate Builder v8.xlsm
	B	C	D	E	F
4	Intumescent Batt and Mastic	1.00	1000	1000	£135.70
5	Mastic Only Penetration	0.01	80	80	£18.90
6	Intumescent Batt and Mastic	0.30	550	545	£68.40
7	Intumescent Batt and Mastic	0.12	400	300	£18.90
8	Intumescent Batt and Mastic	0.15	250	600	£18.90
9
10					£260.80
11
12
BoM Quote Checker

Cell Formulas
Range	Formula
F4	F4	=INDEX('Sell Rates (2)'!C:C,MATCH(1,($B$4='Sell Rates (2)'!A:A)*(C4='Sell Rates (2)'!B:B),0))
C4:C5,C7:C8	C4	=D4*E4/1000000
F6	F6	=INDEX('Sell Rates (2)'!C:C,MATCH(1,($H$4='Sell Rates (2)'!A:A)*(C6='Sell Rates (2)'!B:B),0))
F10	F10	=SUM(F4:F9)

 

[awoohaw]

Can you post the formula that is giving the error as well the cells where it occurs, and the cells that contain the arguments for the calculation? Please.

 

[kevin9999]

can you match a =>

Try changing the last argument of the match from 0 to -1 and see if that works for you.

for example:

='Sell Rates (2)'!B:B),-1))

 

[Peter_SSs]

how would i return the yellow query prices

I'm not sure how you get those last two yellow values?
But does this help?
I recommend that you do not use whole column references in the formulas. I have used 1,000 rows but just make sure that is big enough to accommodate any data you might have.

j4ymf.xlsm
	B	C	D	E	F
4	Intumescent Batt and Mastic	1	1000	1000	135.7
5	Mastic Only Penetration	0.0064	80	80	18.9
6	Intumescent Batt and Mastic	0.29975	550	545	68.4
7	Intumescent Batt and Mastic	0.12	400	300	55.1
8	Intumescent Batt and Mastic	0.15	250	600	55.1
BoM Quote Checker

Cell Formulas
Range	Formula
C4:C8	C4	=D4*E4/1000000
F4:F8	F4	=INDEX(SORT(FILTER('Sell Rates (2)'!B$1:C$1000,('Sell Rates (2)'!A$1:A$1000=B4)*('Sell Rates (2)'!B$1:B$1000>=C4)),1),1,2)

 

[j4ymf]

Hello Peter

sorry for the late reply, but i would like to say thank you as this works fantastic

" Thank you "

 

[Peter_SSs]

You're welcome. Thanks for the follow-up (it doesn't need to be immediate).