Finding Matching Values from Multiple Columns Based on Criteria

What output would you want for your compiled list?

You could just use three columns of formulas:

In B2: =VLOOKUP(A2,Sheet1!A:B,2,FALSE)
In C2: =VLOOKUP(A2,Sheet2!A:B,2,FALSE)
In D2: =VLOOKUP(A2,Sheet3!A:B,2,FALSE)

Then copy those down to match your list. Then in E2, array-enter (enter using Ctrl-Shift-Enter) the formula

=LEFT(B2,MAX(IF(LEFT(B2,COLUMN(A1:J1))=LEFT(C2,COLUMN(A1:J1)),IF(LEFT(C2,COLUMN(A1:J1))=LEFT(D2,COLUMN(A1:J1)),COLUMN(A1:J1),0),0)))

and copy that down. That will extract the common strings from the values returned by the first formulas.

For Z100C, all these values occur on all three sheets - so which would you want?

100-C09SKF01 A
100-C09SKJ10 A
100-C09UKF400 A
100-C09V01 A
100-C09Y200 A
100-C12D300 A
100-C12K300 A
100-C12KA200 A
100-C12KF11
100-C23KN10M C
100-C23SJ10 C
100-C23SKF01 C
100-C23SKF200 C
100-C23SKJ10 C
100-C23UK01 C
100-C23UKF400 C
100-C23UT01 C
100-C23UT10 C
100-C23UV01 C
100-C23UX200 C
100-C30UT00 C
100-C30V00 C
100-C37KA01 C
100-C37T01 C
100-C37UF00 C
100-C37UT10 C
100-C37UV00 C
100-C37V10 C
100-C37W01 C
100-C40K400 A
100-C40KY400 A
100-C43KN10 A
100-C43UKF10 A
100-C43V01 A
100-C60DT01 B
100-C60UF00 B
100-C60UKF10 B
100-C60UT00 B
100-C60W00 B
100-C72DF00 B
100-C72KP00 B
100-C72V00 B
100-C72Y00 B
100-C85DG00 A
100-C85KY01 A
100-C85UDD00 A
100-C85UV00 A
100-C90DA200 A
100-C90KJ200 A
100-C90UD400 A
100-C90VA200 A
100-CR09D01 A
100-CR09KD01 A
100-CR09ZJ01 A
100-CR09ZJ10 A
100-CR12KD01 A
100-CR12KF01 A
100-CR12ZJ01 A
100-CR12ZJ10 A
100-CR16D10 A
100-CR16ZJ10 A
22-302-325-95
22-302-331-95
22-302-331-98
22-302-619-95
22-302-631-95
22-302-631-97
22-302-631-98
22-322-331-98
22-322-631-95
22-362-707-95
22-362-801-95
22-362-807-98
22-362-813-98
PN-183029
PN-212854
PN-214788
PN-267751
PN-277861
PN-304295
PN-310680
PN-310681
PN-310687
PN-310690
PN-332406
PN-33285
PN-56422
PN-65192
PN-73631

I amade it 89 matching values for that code. You need a rethink, I think!!!

With a helper column in Sheet1, C2, copied down:
=COUNTIFS($A$2:$A$40000,A2,$B$2:$B$40000,B2)+COUNTIFS(Sheet2!$A$2:$A$40000,A2,Sheet2!$B$2:$B$40000,B2)+COUNTIFS(Sheet3!$A$2:$A$40000,A2,Sheet3!$B$2:$B$40000,B2)

This formula in Compiled, B2, copied down:

=IFERROR(INDEX(Sheet1!$B$2:$B$8911,MATCH(1,INDEX((Sheet1!$A$2:$A$8911=A2)*(Sheet1!C$2:C$8911=3),0),0)),"")

Here is your file, with an extra column of formulas on Sheet1. I had to delete a few thousand rows of Sheet3 to get the file small enough to upload as an attachement.

all 3 sheets in sheet3, =COUNTIFS(B:B,B2,C:C,C2) and autofilter

You're welcome and thanks for the rep.