Generating report using multiple criteria

[lai_arceo]

Hello, everyone.

I am hoping someone could help me with an index/match function to generate a report based on multiple criteria. I actually want to be able to copy a whole column of data based on 3 criteria that the user will fill.

Fig. 1 contains my database and Fig. 2 is the report I want to generate based on the week #, date and legend. I need the whole column copied to the "Reports" sheet depending on the 3 criteria.

Appreciate your kind assistance.

 

[Glenn Kennedy]

Assuming you have O365 (you didn't tell us what product you use)

47	47	47
13-Nov	14-Nov	15-Nov
*	**	***
Q	A	Z
W	S	X
E	D	C
R		V
T		B
		N
		M

Book1
	A	B
1		47
2		13-Nov
3		*
4		Q
5		W
6		E
7		R
8		T
9
Sheet2

Cell Formulas
Range	Formula
B4:B8	B4	=TOCOL(FILTER(db!A4:D100,(db!A1:D1=B1)*(db!A2:D2=B2)*(db!A3:D3=B3)),1)
Dynamic array formulas.

 

[lai_arceo]

Assuming you have O365 (you didn't tell us what product you use)

47	47	47
13-Nov	14-Nov	15-Nov
*	**	***
Q	A	Z
W	S	X
E	D	C
R		V
T		B
		N
		M

Book1
	A	B
1		47
2		13-Nov
3		*
4		Q
5		W
6		E
7		R
8		T
9
Sheet2

Cell Formulas
Range	Formula
B4:B8	B4	=TOCOL(FILTER(db!A4:D100,(db!A1:D1=B1)*(db!A2:D2=B2)*(db!A3:D3=B3)),1)
Dynamic array formulas.

Hi, Glenn. Thank you for replying. I am using O365. The formula is generating an error report. Please see attached image.

 

[Glenn Kennedy]

What VERSION of O365 do you have? The formula will work with 2208 and above. Depending on WHERE you are, you may need ; instead of , As you can see from Post 2... it was working fine for me.

 

[Glenn Kennedy]

Are the ranges in the formula appropriate for your real data?

 

[lai_arceo]

What VERSION of O365 do you have? The formula will work with 2208 and above. Depending on WHERE you are, you may need ; instead of , As you can see from Post 2... it was working fine for me.

We're using Windows 10 Enterprise.

Is there any other way to do this? An index/match function perhaps?

 

[Glenn Kennedy]

Windows 10 Enterprise is your Operating System... not your Excel product. Go to File/Account/About Excel. What version do you have?

 

[lai_arceo]

Windows 10 Enterprise is your Operating System... not your Excel product. Go to File/Account/About Excel. What version do you have?

MS Excel 2019

 

[Glenn Kennedy]

You said O365 in Post 3... and now you tell us that it's Excel 2019. Back to the drawing board.

 

[lai_arceo]

You said O365 in Post 3... and now you tell us that it's Excel 2019. Back to the drawing board.

It's Excel 2019. Sorry.