Help in Generating a Formula

[omarmalik85]

Hi Guys,

I need help in creating a formula using the below sheet. Whereby if I select the three options and if all three selections match I get the result. I have put two Examples on the left to explain what I want. I would really appreciate your help in it.

Feel free to ask if you need more information.

Test Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	130									EXAMPLE 1			EXAMPLE 2
3		Size / Qty	10000	15000	20000	25000	50000	100000		OPTION1:	130		OPTION1:	150
4		A5	$80	$112	$160	$200	$350	$650		OPTION2:	25000		OPTION2:	10000
5		A4	$200	$280	$320	$395	$695	$1,295		OPTION3:	A4		OPTION3:	A3
6		A4 ext	$325	$455	$420	$525	$950	$1,700		Result:	395		Result:	450
7		B4	$325	$455	$420	$525	$950	$1,700
8		A3	$350	$510	$590	$695	$1,295	$2,450
9
10	150
11		Size / Qty	10000	15000	20000	25000	50000	100000
12		A5	$125	$175	$208	$260	$425	$800
13		A4	$280	$392	$380	$475	$900	$1,600
14		A4 ext	$395	$553	$480	$600	$1,050	$1,950
15		B4	$395	$553	$480	$600	$1,050	$1,950
16		A3	$450	$630	$720	$900	$1,550	$2,900
17
18	170
19		Size / Qty	10000	15000	20000	25000	50000	100000
20		A5	$160	$224	$260	$325	$500	$950
21		A4	$300	$420	$440	$550	$950	$1,750
22		A4 ext	$450	$630	$556	$695	$1,225	$2,300
23		B4	$450	$630	$556	$695	$1,225	$2,300
24		A3	$450	$630	$740	$925	$1,700	$3,200
Sheet1

 

[jasonb75]

Your layout is not formula friendly, this is about the closest you will get that will work reliably.

sumif weeknum (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2										EXAMPLE 1			EXAMPLE 2
3		Size / Qty	10000	15000	20000	25000	50000	100000		OPTION1:	130		OPTION1:	150
4	130	A5	80	112	160	200	350	650		OPTION2:	25000		OPTION2:	10000
5	130	A4	200	280	320	395	695	1295		OPTION3:	A4		OPTION3:	A3
6	130	A4 ext	325	455	420	525	950	1700		Result:	395		Result:	450
7	130	B4	325	455	420	525	950	1700
8	130	A3	350	510	590	695	1295	2450
9
10
11		Size / Qty	10000	15000	20000	25000	50000	100000
12	150	A5	125	175	208	260	425	800
13	150	A4	280	392	380	475	900	1600
14	150	A4 ext	395	553	480	600	1050	1950
15	150	B4	395	553	480	600	1050	1950
16	150	A3	450	630	720	900	1550	2900
17
18
19		Size / Qty	10000	15000	20000	25000	50000	100000
20	170	A5	160	224	260	325	500	950
21	170	A4	300	420	440	550	950	1750
22	170	A4 ext	450	630	556	695	1225	2300
23	170	B4	450	630	556	695	1225	2300
24	170	A3	450	630	740	925	1700	3200
Sheet8

Cell Formulas
Range	Formula
K6,N6	K6	=INDEX($C$4:$H$24,MATCH(K3&"|"&K5,$A$4:$A$24&"|"&$B$4:$B$24,0),MATCH(K4,$C$3:$H$3,0))

 

[omarmalik85]

Hi,

Thanks for the prompt reply. But I am getting the #VALUE! when using your formula.

Your layout is not formula friendly, this is about the closest you will get that will work reliably.

sumif weeknum (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2										EXAMPLE 1			EXAMPLE 2
3		Size / Qty	10000	15000	20000	25000	50000	100000		OPTION1:	130		OPTION1:	150
4	130	A5	80	112	160	200	350	650		OPTION2:	25000		OPTION2:	10000
5	130	A4	200	280	320	395	695	1295		OPTION3:	A4		OPTION3:	A3
6	130	A4 ext	325	455	420	525	950	1700		Result:	395		Result:	450
7	130	B4	325	455	420	525	950	1700
8	130	A3	350	510	590	695	1295	2450
9
10
11		Size / Qty	10000	15000	20000	25000	50000	100000
12	150	A5	125	175	208	260	425	800
13	150	A4	280	392	380	475	900	1600
14	150	A4 ext	395	553	480	600	1050	1950
15	150	B4	395	553	480	600	1050	1950
16	150	A3	450	630	720	900	1550	2900
17
18
19		Size / Qty	10000	15000	20000	25000	50000	100000
20	170	A5	160	224	260	325	500	950
21	170	A4	300	420	440	550	950	1750
22	170	A4 ext	450	630	556	695	1225	2300
23	170	B4	450	630	556	695	1225	2300
24	170	A3	450	630	740	925	1700	3200
Sheet8

Cell Formulas
Range	Formula
K6,N6	K6	=INDEX($C$4:$H$24,MATCH(K3&"|"&K5,$A$4:$A$24&"|"&$B$4:$B$24,0),MATCH(K4,$C$3:$H$3,0))

 

[jasonb75]

There is nothing in the formula to cause a #VALUE! error, you would get a #N/A error if there was no match for the selected options, but no other errors are possible.

 

[Fluff]

You may need to confirm the formula with Ctrl Shift Enter, depending on which version of Xl you are using.