Index Match function to derive the entire list in a table based on a selected variable/s.

[93arpan]

Hi Everyone,

I'm working on this unique case.

I've a table (raw data) in which I have Column 4 columns named Country, Supplier, Parts, and Net Price.

Country	Supplier	Parts	Net Price
Germany	Intel	Part 1	2.30
Germany	Intel	Part 2	2.34
Germany	Spirax	Part 1.A	2.38
Germany	Romoss	Part A.A	2.42
Germany	Romoss	Part B.A	2.46
Germany	Romoss	Part C.A	2.50
Germany	Zeiss	Part D	2.54
Germany	Gem Ltd	Part E.A	2.58
Spain	Gem Ltd	Part 1	5.00
Spain	Gem Ltd	Part 2	5.04
Spain	Gem Ltd	Part 3	5.08
Spain	Discount Warehouse	Part 4.A	4.20
Spain	Discount Warehouse	Part 6.A	4.24
Spain	Discount Warehouse	Part A.1	4.28
Spain	Discount Warehouse	Part A.6	4.32

I'm trying to extract the values, in this instance all the Parts, if I select 'Spain' as a country and 'Supplier Discount Warehouse' as the Supplier

Country	Supplier	Parts	Net Price
Spain	Discount Warehouse	Part 4.A	4.20
	Discount Warehouse	Part 6.A	4.24
	Discount Warehouse	Part A.1	4.28
	Discount Warehouse	Part A.6	4.32

Is there a way to do this using excel formula (Index, Match, or anything)?

 

[Anonymous1378]

Try the FILTER() function?

Book3
	A	B	C	D
1	Country	Supplier	Parts	Net Price
2	Germany	Intel	Part 1	2.3
3	Germany	Intel	Part 2	2.34
4	Germany	Spirax	Part 1.A	2.38
5	Germany	Romoss	Part A.A	2.42
6	Germany	Romoss	Part B.A	2.46
7	Germany	Romoss	Part C.A	2.5
8	Germany	Zeiss	Part D	2.54
9	Germany	Gem Ltd	Part E.A	2.58
10	Spain	Gem Ltd	Part 1	5
11	Spain	Gem Ltd	Part 2	5.04
12	Spain	Gem Ltd	Part 3	5.08
13	Spain	Discount Warehouse	Part 4.A	4.2
14	Spain	Discount Warehouse	Part 6.A	4.24
15	Spain	Discount Warehouse	Part A.1	4.28
16	Spain	Discount Warehouse	Part A.6	4.32
17
18	Country	Supplier	Parts	Net Price
19	Spain	Discount Warehouse	Part 4.A	4.2
20			Part 6.A	4.24
21			Part A.1	4.28
22			Part A.6	4.32
Sheet1

Cell Formulas
Range	Formula
C19:D22	C19	=FILTER(C2:D16,(A2:A16=A19)*(B2:B16=B19))
Dynamic array formulas.

 

[kevin9999]

If you have access to the Filter function, then here's one possibility:

Book1
	A	B	C	D	E	F
1	Country	Supplier	Parts	Net Price	Country	Supplier
2	Germany	Intel	Part 1	2.3	Spain	Discount Warehouse
3	Germany	Intel	Part 2	2.34
4	Germany	Spirax	Part 1.A	2.38
5	Germany	Romoss	Part A.A	2.42
6	Germany	Romoss	Part B.A	2.46
7	Germany	Romoss	Part C.A	2.5
8	Germany	Zeiss	Part D	2.54
9	Germany	Gem Ltd	Part E.A	2.58
10	Spain	Gem Ltd	Part 1	5
11	Spain	Gem Ltd	Part 2	5.04
12	Spain	Gem Ltd	Part 3	5.08
13	Spain	Discount Warehouse	Part 4.A	4.2
14	Spain	Discount Warehouse	Part 6.A	4.24
15	Spain	Discount Warehouse	Part A.1	4.28
16	Spain	Discount Warehouse	Part A.6	4.32
17
18	Country	Supplier	Parts	Net Price
19	Spain	Discount Warehouse	Part 4.A	4.2
20	Spain	Discount Warehouse	Part 6.A	4.24
21	Spain	Discount Warehouse	Part A.1	4.28
22	Spain	Discount Warehouse	Part A.6	4.32
23
24	Country	Supplier	Parts	Net Price
25	Spain	Discount Warehouse	Part 4.A	4.2
26	Spain	Discount Warehouse	Part 6.A	4.24
27	Spain	Discount Warehouse	Part A.1	4.28
28	Spain	Discount Warehouse	Part A.6	4.32
Sheet1

Cell Formulas
Range	Formula
A18:D22	A18	=VSTACK({"Country","Supplier","Parts","Net Price"},FILTER($A$2:$D$16,($A$2:$A$16="Spain")*($B$2:$B$16="Discount Warehouse"),""))
A24:D28	A24	=VSTACK({"Country","Supplier","Parts","Net Price"},FILTER($A$2:$D$16,($A$2:$A$16=E2)*($B$2:$B$16=F2),""))
Dynamic array formulas.

 

[93arpan]

Thank you very much you're such a legend.
This totally works