Lookup Approximate match against multiple criteria!

[Kamran01]

Hello Everyone,

I have a data table and i want to find approximate match against that table,

CATEGORY	DENIM	DENIM	NON_DENIM	NON_DENIM
RANGE	NORMAL WASH	HEAVY WASH	WASH	DYE
999	8.00%​	12.00%​	8.00%​	12.00%​
2,999	6.00%​	8.00%​	6.00%​	8.00%​
4,999	4.00%​	6.00%​	4.00%​	6.00%​
9,999	3.00%​	4.50%​	3.00%​	4.50%​
14,999	2.50%​	4.00%​	2.50%​	4.00%​
29,999	2.00%​	3.50%​	2.00%​	3.50%​
49,999	1.50%​	3.00%​	1.50%​	3.00%​
100,000	1.00%​	2.50%​	1.00%​	2.50%​
200,000	0.50%​	2.00%​	0.50%​	2.00%​
1,000,000	0.50%​	2.00%​	0.50%​	2.00%​

I want below result, if quantity is less then their maximum range suppose 5000 is less then their 9,999 then result should be like below table.

QTY	TYPES	WASH_CAT	RESULT REQUIRED
5000	NON_DENIM	WASH	3.00%

Looking forward for your kind response in this regard,

Best Regard,
Kamran Noor

 

[Special-K99]

Ignore this my solution didnt work

 

[Marcelo Branco]

Maybe...

Book1
	A	B	C	D	E
1	CATEGORY	DENIM	DENIM	NON_DENIM	NON_DENIM
2	RANGE	NORMAL WASH	HEAVY WASH	WASH	DYE
3	999	0,08	0,12	0,08	0,12
4	2999	0,06	0,08	0,06	0,08
5	4999	0,04	0,06	0,04	0,06
6	9999	0,03	0,045	0,03	0,045
7	14999	0,025	0,04	0,025	0,04
8	29999	0,02	0,035	0,02	0,035
9	49999	0,015	0,03	0,015	0,03
10	100000	0,01	0,025	0,01	0,025
11	200000	0,005	0,02	0,005	0,02
12	1000000	0,005	0,02	0,005	0,02
13
14	QTY	TYPES	WASH_CAT	RESULT REQUIRED
15	5000	NON_DENIM	WASH	0,03
Plan4

Cell Formulas
Range	Formula
D15	D15	=INDEX(INDEX(B$3:E$12,0,MATCH(C15,B$2:E$2,0)),MATCH(A15,A$3:A$12)+1*ISNA(MATCH(A15,A$3:A$12,0)))

M.

 

[Kamran01]

Thanks @Marcelo Branco it's done, and even i have solved this mystery with my own mind.

{=INDEX($B$5:$E$14,IFERROR(MATCH(A18-1,$A$5:$A$14,1)+1,1),MATCH(B18&C18,B3:E3&B4:E4,0))}

Can you please validate my answer too is it driving OK?

Thank you,
Kamran Noor

 

[Marcelo Branco]

Thanks @Marcelo Branco it's done, and even i have solved this mystery with my own mind.

{=INDEX($B$5:$E$14,IFERROR(MATCH(A18-1,$A$5:$A$14,1)+1,1),MATCH(B18&C18,B3:E3&B4:E4,0))}

Can you please validate my answer too is it driving OK?

Thank you,
Kamran Noor

From your data sample it seemed to me that the Categories (Normal Wash, Heavy Wash, Wash, Dye) are unique.

If I am correct, it is not necessary to use the Type criterion (DENIM, NON_DENIN) - so the category would suffice as a criterion to search for the correct column.

M.

 

[Kamran01]

Thank you very much