Extracting data using multiple criteria

[jdhfch]

Hi,

I am trying to extract all data from a list based on 2 criteria. Eg, The first is an account number (in B1) and the second is a product code (in B8) and I am wanting to extract all order numbers for that account only, in the source data the account number is in column D, the product is in Column C and the data I want to extract is in column F. It is possible that there will be multiple orders..

Please can anyone help?

 

[Toadstool]

Hi Jdhfch,

If you can update your profile with your Excel version it would help. I'm using Excel 2016 but if you have 365 there's a simpler solution.

JDHfch.xlsx
	A	B	C	D	E	F	G	H
1	Account No.		Product Code	Account No.		Order No.		Extract
2	AC123		XX222	AC222		ON102		ON106
3			XX828	AC123		ON103		ON104
4			XX090	AC222		ON104		ON111
5			XX828	AC222		ON105		ON112
6			XX090	AC123		ON106
7	Product Code		XX828	AC222		ON107
8	XX090		XX090	AC123		ON104
9			XX828	AC123		ON109
10			XX090	AC222		ON110
11			XX090	AC123		ON111
12			XX090	AC123		ON112
13
Sheet1

Cell Formulas
Range	Formula
H2:H13	H2	=IFERROR(INDEX($F$2:$F$99999,AGGREGATE(15,6,ROW($C$2:$C$99999)-ROW($C$1)/(($D$2:$D$99999=$A$2)*($C$2:$C$99999=$A$8)),ROW()-ROW($H$1))),"")