Multi Criteria Lookup

[aarti_rto]

Hello Experts,

i have below dataset , which i need to filter with multiple ( criteria's and expected results mentioned below ) but in different worksheet.

Suppler Name	Part No	Part Name	Rate/ Pc	Monthly Schedule
ABC	ABC1	test 1	10​	339​
XYZ	XYZ2	test 2	10​	677​
ABC	ABC3	test 3	8​	236​
CBZ	ABC1	test 4	6​	259​
ABC	ABC5	test 5	3​	252​
XYZ	XYZ6	test 6	10​	785​
ABC	ABC7	test 7	7​	885​
BCD	ABC1	test 8	9​	775​
ABC	ABC9	test 9	2​	412​

Suppose if i type / dropdown in A2 and B2 then A7 to E7 should display below said results

	Criteria 1
	A	B
1​	Suppler Name	Part No
2​	ABC	ABC1
3​
4​	Results for criterial 1
5​	A	B	C	D	E
6​	Suppler Name	Part No	Part Name	Rate/ Pc	Monthly Schedule
7​	ABC	ABC1	test 1	10​	339​

Suppose if i type / dropdown only in A2 then A6 to E10 should display below said results

	A	B	C	D	E
	Criteria 2
	Suppler Name	Part No
1​	ABC
2​
3​
4​	Results for criterial 2
5​	Suppler Name	Part No	Part Name	Rate/ Pc	Monthly Schedule
6​	ABC	ABC1	test 1	10​	339​
7​	ABC	ABC3	test 3	8​	236​
8​	ABC	ABC5	test 5	3​	252​
9​	ABC	ABC7	test 7	7​	885​
10​	ABC	ABC9	test 9	2​	412​

Suppose if i type / dropdown only in B1 then A5 to E7 should display below said results

	A	B	C	D	E
	Criteria 3
	Suppler Name	Part No
1​		ABC1
2​
3​	Results for criterial 3
4​	Suppler Name	Part No	Part Name	Rate/ Pc	Monthly Schedule
5​	ABC	ABC1	test 1	10​	339​
6​	CBZ	ABC1	test 4	6​	259​
7​	BCD	ABC1	test 8	9​	775​

and last criteria , if i don't add any filter all data should display as per dataset

 

[Toadstool]

Hi Aarti_rto,

Here's your data

aarti_rto2.xlsx
	A	B	C	D	E
1	Suppler Name	Part No	Part Name	Rate/ Pc	Monthly Schedule
2	ABC	ABC1	test 1	10	339
3	XYZ	XYZ2	test 2	10	677
4	ABC	ABC3	test 3	8	236
5	CBZ	ABC1	test 4	6	259
6	ABC	ABC5	test 5	3	252
7	XYZ	XYZ6	test 6	10	785
8	ABC	ABC7	test 7	7	885
9	BCD	ABC1	test 8	9	775
10	ABC	ABC9	test 9	2	412
Data

Here's the query

aarti_rto2.xlsx
	A	B	C	D	E
1	Suppler Name	Part No
2	XYZ
3
4	Suppler Name	Part No	Part Name	Rate/ Pc	Monthly Schedule
5	XYZ	XYZ2	test 2	10	677
6	XYZ	XYZ6	test 6	10	785
7
8
9
10
11
12
13
14
15
16
Query

Cell Formulas
Range	Formula
A5:E16	A5	=IFERROR(INDEX(Data!A$2:A$9999,AGGREGATE(15,6,ROW(Data!$A$2:$A$9999)-ROW(Data!$A$1)/(((Data!$A$2:$A$9999=$A$2)+($A$2=""))*((Data!$B$2:$B$9999=$B$2)+($B$2=""))*(Data!$A$2:$A$9999<>"")),ROW()-ROW($A$4))),"")

 

[aarti_rto]

hi,
thanks for the formula , it's working..

 

[aarti_rto]

hi,
i tried above formula to my other workbook , which contains data in number as below data set.

i tried two conditions one with column "FG" and second with column "Part No" but as these are number i need to type exact number to search..

is it possible that if i type any one or two digits from the number and filter will applicable??

FG	Type	Sr No	Part No	Qty	UOM
0003835000	Finish Goods	1	0003835000	1​	Piece (PC)​
0003835000	Extruded Part	2	900211012	1​	Piece (PC)​
0003835000	Forging	3	900120009	1​	Piece (PC)​
0003855000	Finish Goods	1	0003855000	1​	Piece (PC)​
0003855000	Forging	2	900120009	1​	Piece (PC)​
0003855000	Scrap	3	7000502500	-0.123​	Kilogram (KG)​
0003915000	Finish Goods	1	0003915000	1​	Piece (PC)​
0003915000	Forging	2	900120009	1​	Piece (PC)​
0003915000	Scrap	3	7000502500	-0.124​	Kilogram (KG)​
0003939000​	Finish Goods​	1​	0003939000​	1​	Piece (PC)​
0003939000​	scrap​	2​	9990000096​	-0.001​	Kilogram (KG)​
0003939000​	Raw Material​	3​	9400000400​	0.01​	Kilogram (KG)​
0004049900​	Finish Goods​	1​	0004049900​	1​	Piece (PC)​
0004049900​	component​	2​	0020045000​	1​	Piece (PC)​
0004049900​	component​	3​	0900409900​	1​	Piece (PC)​
0004069900​	Finish Goods​	1​	0004069900​	1​	Piece (PC)​
0004069900​	component​	2​	0900409900​	1​	Piece (PC)​
0004069900​	component​	3​	910024005000​	1​	Piece (PC)​