Index Match across worksheets with criteria

[ChemistryBMS]

Hi All,

Apologies if the title doesn't explain my query best, but I have data in separate worksheets and want to create a search filter with set criteria. Cell A2 is a data validation list to select the desired test. Cell B2 is the select the mathematical operator for greater or less than. Cell C2 for a numeric value to be entered manually.

Sample finder snapshot.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Test	Operator	Value		Sample ID	Result	Location		I am aiming to make an automatic population of sample IDs with result and location in the open table. We can pull data from our IT system and it provides the data in the templates seen in the other worksheets. The yellow tab is the archiving position for each sample. The blue test filter should look in the worksheet 'CALCIUM' The green operator filter is to distinguish a greater or less than The orange value is to set the value the table should look against With this filter on, the values that should come back are: - 123459 | 2.9 | RACK 4 POS 2 - 123460 | 3.0 | RACK 4 POS 4
2	CALCIUM	>	2.6
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Finder

Cells with Data Validation
Cell	Allow	Criteria
A2	List	='Criteria filters'!$A$2:$A$3
B2	List	='Criteria filters'!$C$2:$C$3

The samples have archive positions seen in this table (worksheet 'Archive position'). I want to return the values in column I (result).

Sample finder snapshot.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Last name	First name	Birth date	Sex	Sample	Test	Analyser	Result datetime	Result	Unit	Request department	Request physician code
2	PATIENT 1	TEST	01/01/2000	M	123456	ARCHIVE	MANUAL ARCHIVE	01/07/2023 12:00	RACK 1 POS 3		A&E	DR TEST
3	PATIENT 2	TEST	02/01/2000	M	123457	ARCHIVE	MANUAL ARCHIVE	02/07/2023 12:00	RACK 2 POS 1		A&E	DR TEST
4	PATIENT 3	TEST	03/01/2000	M	123458	ARCHIVE	MANUAL ARCHIVE	03/07/2023 12:00	RACK 3 POS 5		GP	DR TEST
5	PATIENT 4	TEST	04/01/2000	M	123459	ARCHIVE	MANUAL ARCHIVE	04/07/2023 12:00	RACK 4 POS 2		GP	DR TEST
6	PATIENT 5	TEST	05/01/2000	M	123460	ARCHIVE	MANUAL ARCHIVE	05/07/2023 12:00	RACK 4 POS 4		A&E	DR TEST
Archive position

Tests have their lab number and results in another worksheet, named the same as the test. This worksheet would be titled 'CALCIUM'.

Sample finder snapshot.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Last name	First name	Birth date	Sex	Sample	Test	Analyser	Result datetime	Result	Unit	Request department	Request physician code
2	PATIENT 1	TEST	01/01/2000	M	123456	CALCIUM	ANALYSER 1	01/07/2023 12:00	2.5	MMOL/l	A&E	DR TEST
3	PATIENT 2	TEST	02/01/2000	M	123457	CALCIUM	ANALYSER 2	02/07/2023 12:00	2.6	MMOL/l	A&E	DR TEST
4	PATIENT 3	TEST	03/01/2000	M	123458	CALCIUM	ANALYSER 3	03/07/2023 12:00	2	MMOL/l	GP	DR TEST
5	PATIENT 4	TEST	04/01/2000	M	123459	CALCIUM	ANALYSER 4	04/07/2023 12:00	2.9	MMOL/l	GP	DR TEST
6	PATIENT 5	TEST	05/01/2000	M	123460	CALCIUM	ANALYSER 5	05/07/2023 12:00	3.1	MMOL/l	A&E	DR TEST
CALCIUM

I have uploaded from my personal PC on 365 but need the formula(s) to be compatible with my work version (office 2013).

Thanks, ChemistryBMS

Also asked here Index match with filters

 

[Fluff]

How about

Sample finder snapshot.xlsx
	A	B	C	D	E	F	G
1	Test	Operator	Value		Sample ID	Result	Location
2	CALCIUM	>	2.6		123459	2.9	RACK 4 POS 2
3					123460	3.1	RACK 4 POS 4
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Finder

Cell Formulas
Range	Formula
E2:E20	E2	=IFERROR(INDEX(INDIRECT("'"&$A$2&"'!E:E"),AGGREGATE(15,6,ROW(INDIRECT("'"&$A$2&"'!I2:I100"))/(IF($B$2="<",INDIRECT("'"&$A$2&"'!I2:I100")<$C$2,INDIRECT("'"&$A$2&"'!I2:I100")>$C$2)),ROWS(E$2:E2))),"")
F2:F20	F2	=IFERROR(INDEX(INDIRECT("'"&$A$2&"'!I:I"),AGGREGATE(15,6,ROW(INDIRECT("'"&$A$2&"'!I2:I100"))/(IF($B$2="<",INDIRECT("'"&$A$2&"'!I2:I100")<$C$2,INDIRECT("'"&$A$2&"'!I2:I100")>$C$2)),ROWS(E$2:E2))),"")
G2:G20	G2	=IF(E2="","",INDEX('Archive position'!$I$2:$I$100,MATCH(E2,'Archive position'!$E$2:$E$100,0)))

Cells with Data Validation
Cell	Allow	Criteria
A2	List	='Criteria filters'!$A$2:$A$3
B2	List	='Criteria filters'!$C$2:$C$3

 

[ChemistryBMS]

Hi Fluff,

Many thanks for the prompt response and working that back into my original document worked the first time.

We also have values where the measurement is below a limit. See below in the image, what would be the best way to omit this data?

Thanks, ChemistryBMS

 

[Fluff]

Do you just want the 3rd row returned?

 

[ChemistryBMS]

Yes, plus values that have the > / < applied too, therefore row 7 would be returned too.

However, if I change B2 to <, the results of <3 would return.

 

[Fluff]

The problem is values such as >120000 and <3 are text & not numbers & so it's no simple matter to test whether they are greater then (or less than) a particular value.