excel - Return array of all matches between two ranges -

ok trying take common tutorial array formula step further cannot figure out how.

essentially have set of sheets values below:

| sheet 1                       || sheet 2              | | products(1)   | product group || products(2)   | data | |               |               ||               |      | | 100           | 1             || 100           | abc  | | 200           | 2             || 200           | def  | | 300           | 3             || 200           | ghi  | | 400           | 3             || 500           | jkl  | | 500           | 2             || 400           | mno  | 

sheet 1 lists parameters classify each product , uses assign each product group. products unique index key.

sheet 2 tracking list of every time product run, how did. therefore product numbers may show multiple times or not @ all.

i have third sheet in product number entered, group number calculated, , sheet 1 searched products group number , list returned using array formula (using tutorial http://thinketg.com/how-to-return-multiple-match-values-in-excel-using-index-match-or-vlookup/ shows on over line different people). call "column k" on sheet 3.

what want take step further , return "data" sheet 2 matches between "sheet 2"!"products(2)" , "sheet 3"!"column k". if "column k" fixed use same formula again , put or statement if expression, because k dynamically populated not sure how find them all.

for clarification, end result ideally show this:

| sheet 3                                                 | | product num   | column k    | column l    | column m    | | (user enters) | (automatic) | (automatic) | (automatic) | | 500           | 200         | 200         | def         | |               | 500         | 200         | ghi         | | product group |             | 500         | jkl         | | (automatic)   |             |             |             | | 2             |             |             |             | 

if compare column vector row vector in array formula compare each value column each value in row. following work because transpose values in sheet3!k1:k[n] row vector before comparing sheet2!$a$1:$a$10000.

sheet1:

sheet2:

sheet3:

formulas in sheet3:

in a5:

=vlookup($a$2,sheet1!$a:$b,2,false) 

in k2 downwards:

{=iferror(index(sheet1!$a$1:$a$10000,small(if(sheet1!$b$1:$b$10000=$a$5,row(sheet1!$b$1:$b$10000)),row(1:1))),"")} 

in l2 downwards:

{=index(sheet2!$a$1:$a$10000,small(if(sheet2!$a$1:$a$10000=transpose($k$1:index($k:$k,max(if($k$1:$k$10000<>"",row($k$1:$k$10000))))),row(sheet2!$a$1:$a$10000)),row(1:1)))} 

in m2 downwards:

{=index(sheet2!$b$1:$b$10000,small(if(sheet2!$a$1:$a$10000=transpose($k$1:index($k:$k,max(if($k$1:$k$10000<>"",row($k$1:$k$10000))))),row(sheet2!$a$1:$a$10000)),row(1:1)))} 

formulas in k2,l2,m2 array formulas. input them without curly brackets , press [ctrl]+[shift]+[enter].

the reference k[n] in sheet3!k1:k[n] computed with

index($k:$k,max(if($k$1:$k$10000<>"",row($k$1:$k$10000)))) 

therein

max(if($k$1:$k$10000<>"",row($k$1:$k$10000))) 

gets largest row number in column k content not equals "".

---

if product numbers ever numeric, easier , possible have results sorted also.

sheet1 , sheet2 see above.

sheet3:

formulas in sheet3:

in a5: see above

in k2 downwards:

{=iferror(small(if(sheet1!$b$2:$b$10000=$a$5,sheet1!$a$2:$a$10000),row(1:1)),"")} 

in l2 downwards:

{=small(if(sheet2!$a$2:$a$10000=transpose($k$2:index($k:$k,match(max($k:$k),$k:$k))),sheet2!$a$2:$a$10000),row(1:1))} 

in m2 downwards:

{=index(sheet2!$b$1:$b$10000,small(if(sheet2!$a$1:$a$10000=$l2,row(sheet2!$a$1:$a$10000)),countif($l$2:$l2,l2)))}