Find matches across multiple worksheets using multiple criteria

[daveasu]

Attempting to search across worksheets for matches on 3 criteria and return salary.

Worksheet names are in cell A1 in each worksheet
Department account numbers are in cell A2 in each worksheet

Employee names are in column A
Titles are in column B
Salary is in column E
Department account numbers are in column F

K7 has the following formula:
=IFERROR(INDEX(E:E,SMALL(IF(ISNUMBER(SEARCH($K$5,F:F))*ISNUMBER(SEARCH(K4,B:B)),ROW(A:A)-MIN(ROW(A:A))+1),ROWS($K$7:K7))),"")

This returns matches for Title and Department account number on this sheet only.

Can an additional match criteria for Employee name in Column A be added that would also search across all sheets?

TEST.xlsm
	A	B	C	D	E	F
1	Department1
2	21-1400
3
4
5			Grade	Months	2020-21
6	Salaries:
7	Employee1	Title1		12	49,854	21-1400-5-135
8	Employee2	Title2		12	24,874	21-1400-5-135
9					-	21-1400-5-135
10					-	21-1400-5-135
11					-	21-1400-5-135
12	Total Full Time				74,728
Department1

Cell Formulas
Range	Formula
E12	E12	=E7+E8+E9+E10+E11

 

[Domenic]

It looks to me that your logic could go like this, based on your data and sample criteria...

1) Find the worksheet whose value in cell A2 equals 21-1400.

2) Within the found worksheet, search for Employee1 in Column A, and Title1 in the corresponding cell in Column B, and then return the corresponding value from Column E.

If so, first enter your sheet names in a range of cells, let's say M3:M5. Then enter the following formula in K7, and confirm with CONTROL+SHIFT+ENTER...

=INDEX($M$3:$M$5,MATCH(TRUE,COUNTIF(INDIRECT("'"&$M$3:$M$5&"'!A2"),K5)>0,0))

Then enter the following formula in K8, and confirm with CONTROL+SHIFT+ENTER...

=INDEX(INDIRECT("'"&$K$7&"'!E7:E100"),MATCH(1,IF(INDIRECT("'"&$K$7&"'!A7:A100")=K3,IF(INDIRECT("'"&$K$7&"'!B7:B100")=K4,1)),0))

Hope this helps!

 

[daveasu]

Domenic - that works perfectly! And the INDIRECT command pulls from the other worksheets! Thank you for your help!

 

[Domenic]

You're very welcome, glad I could help.

And thanks for the feedback.

Cheers!