Best way to query against multiple optional criteria.

[berian]

Hi there,

I want to create a table where a user can enter data for a 'Stock Alert Message'. The data entered will consist of a Stock Code, Group, Type and Category. I would then like to run a query against this table which will only show us Stock Items which have an alert.

I'm struggling with how to populate the the Stock Alert correctly as we would like some data to be omitted from the user table.

If a Stock Code get's entered then a user wouldn't need to enter any further information as that stock alert would only show against that stock code. However a combination of Group, Type and Category can be entered. If a query matches against more than one criteria then all applicable messages would be shown.

Here's an example of what I mean below.

Stock Alert Messages.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		Stock Alert Messages
3		Stock Code	Group	Type	Category	Stock Alert
4		1/2BI12				Test 1
5			PLU			Test 2
6			RLI	CZS		Test 3
7			PLU	1/2	COM	Test 4
8
9		Query 1		Query 2		Query 3		Query 4
10		Stock Code	1/2BI12	Stock Code	10MPF18	Stock Code	CAPANTSQUSW1	Stock Code	1/2CO12
11		Group	PLU	Group	PLU	Group	RLI	Group	PLU
12		Type	1/2	Type	10M	Type	CZS	Type	1/2
13		Category	BIB	Category	PFI	Category		Category	COM
14		Stock Alert	Test 1, Test 2	Stock Alert	Test 2	Stock Alert	Test 3	Stock Alert	Test 2, Test 4
15
Sheet1

I've tried looking at various ways of achieving this including FILTER, TEXTJOIN and XLOOKUP but really having trouble putting it together so it works as required. Feel like I've been going round in circles and any help would be really gratefully accepted! If I can provide anymore clarification just let me know!

Many thanks,
B

 

[berian]

Think I'm getting there with this!

Not sure if I've approached this in the most efficient way, but I've managed to figure out a way to get the data I needed!

If anyone has any pointers to what I could do to improve on this - that would be a big help!

Stock Alert Messages.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		Stock Alert Messages
3		Stockcode	Group	Type	Category	Stock Alert Code	Stock Alert
4		1/2BI12				S	Test 1
5			PLU			G	Test 2
6			RLI	CZS		GT	Test 3
7			PLU	1/2	COM	GTC	Test 4
8
9		Query 1		Query 2		Query 3		Query 4
10		Stockcode	1/2BI12	Stockcode	10MPF18	Stockcode	CAPANTSQUSW1	Stockcode	1/2CO12
11		Group	PLU	Group	PLU	Group	RLI	Group	PLU
12		Type	1/2	Type	10M	Type	CZS	Type	1/2
13		Category	BIB	Category	PFI	Category		Category	COM
14		Stock Alert (Expected)	Test 1, Test 2	Stock Alert	Test 2	Stock Alert	Test 3	Stock Alert	Test 2, Test 4
15
Sheet1

Cell Formulas
Range	Formula
F4:F7	F4	= IF([@Stockcode]<>"","S","") & IF([@Group]<>"","G","") & IF([@Type]<>"","T","") & IF([@Category]<>"","C","")
C14,I14,G14,E14	C14	=TEXTJOIN(", ",TRUE, IF(XLOOKUP(C10,saStcode,alertID,"")="S",XLOOKUP(C10,saStcode,alertMsg,""),""), IF(XLOOKUP(C11,saPgroup,alertID)="G",XLOOKUP(C11,saPgroup,alertMsg,""),""), IF(XLOOKUP(C11&C12,saPgroup&saPType,alertID,"")="GT",XLOOKUP(C11&C12,saPgroup&saPType,alertMsg,""),""), IF(XLOOKUP(C11&C12&C13,saPgroup&saPType&saCategory,alertID,"")="GTC",XLOOKUP(C11&C12&C13,saPgroup&saPType&saCategory,alertMsg,""),""), IF(XLOOKUP(C11&C13,saPgroup&saCategory,alertID,"")="GC",XLOOKUP(C11&C13,saPgroup&saCategory,alertMsg,""),""), IF(XLOOKUP(C12,saPType,alertID,"")="T",XLOOKUP(C12,saPType,alertMsg,""),""), IF(XLOOKUP(C12&C13,saPType&saCategory,alertID,"")="TC",XLOOKUP(C12&C13,saPType&saCategory,alertMsg,""),""), IF(XLOOKUP(C13,saCategory,alertID,"")="C",XLOOKUP(C13,saCategory,alertMsg,""),"") )

Named Ranges
Name	Refers To	Cells
alertID	=data[Stock Alert Code]	I14, G14, E14, C14
alertMsg	=Sheet1!$G$4:$G$7	I14, G14, E14, C14
saCategory	=Sheet1!$E$4:$E$7	F4, I14, G14, E14, C14
saPgroup	=Sheet1!$C$4:$C$7	F4, I14, G14, E14, C14
saPType	=Sheet1!$D$4:$D$7	F4, I14, G14, E14, C14
saStcode	=Sheet1!$B$4:$B$7	F4, I14, G14, E14, C14