Index multiple matches?

[idkbrits]

Dear MrExcel Community,

For years I've been able to find answers to VBA/Excel scenarios from you and because of this I never registered or sent any posts.

I could however not find a solution to my latest problem and hope you can assist me.

Here are the scematics.

I have one workbook with two active sheets. The first sheet (Sheet1) is used to import data from a text file into columns (This works perfectly with a Action button and Macro assigned to it) **Note, I have chosen not to add column headers in sheet1.

The next Action Button I have takes the values in Sheet1!A:A and populates Sheet2 from A3 with unique values.

The next step in my mini project is to take the data in Sheet1 which is arranged as shown in table1 and input it into the structure on sheet2 as shown in table2.

TABLE1:[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SYSTEM1
[/TD]
[TD]Check1
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]SYSTEM1
[/TD]
[TD]Check2
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]SYSTEM1
[/TD]
[TD]Check3
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]SYSTEM2
[/TD]
[TD]Check1
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]SYSTEM2
[/TD]
[TD]Check2
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]SYSTEM2
[/TD]
[TD]Check3
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]SYSTEM3
[/TD]
[TD]Check1
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]SYSTEM3
[/TD]
[TD]Check2
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]SYSTEM3
[/TD]
[TD]Check3
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]SYSTEM4
[/TD]
[TD]Check1
[/TD]
[TD]Value
[/TD]
[/TR]
</tbody>[/TABLE]


TABLE2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Headers
[/TD]
[TD]Check1
[/TD]
[TD]Check2
[/TD]
[TD]Check3
[/TD]
[/TR]
[TR]
[TD]System1
[/TD]
[TD]Value
[/TD]
[TD]Value
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]System2
[/TD]
[TD]Value
[/TD]
[TD]Value
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]System3
[/TD]
[TD]Value
[/TD]
[TD]Value
[/TD]
[TD]Value
[/TD]
[/TR]
</tbody>[/TABLE]


Please advise if you'll be able to assist, hope I've explained my situation well enough.

Many Thanks!

 

[Redwolfx]

With a little manipulation This should work for you Excel Lookup and Return Multiple Matches

 

[jamtay317]

Excel 2010

	A	B	C	D	E	F	G	H
TABLE1:	TABLE2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFFFF"]Column1[/TD]
[TD="bgcolor: #FFFFFF"]Column2[/TD]
[TD="bgcolor: #FFFFFF"]Column3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Headers[/TD]
[TD="bgcolor: #FFFFFF"]Check1[/TD]
[TD="bgcolor: #FFFFFF"]Check2[/TD]
[TD="bgcolor: #FFFFFF"]Check3[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FFFFFF"]SYSTEM1[/TD]
[TD="bgcolor: #FFFFFF"]Check1[/TD]
[TD="bgcolor: #FFFFFF"]Value 1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]System1[/TD]
[TD="bgcolor: #FFFFFF"]Value 1[/TD]
[TD="bgcolor: #FFFFFF"]Value 2[/TD]
[TD="bgcolor: #FFFFFF"]Value 3[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FFFFFF"]SYSTEM1[/TD]
[TD="bgcolor: #FFFFFF"]Check2[/TD]
[TD="bgcolor: #FFFFFF"]Value 2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]System2[/TD]
[TD="bgcolor: #FFFFFF"]Value 4[/TD]
[TD="bgcolor: #FFFFFF"]Value 5[/TD]
[TD="bgcolor: #FFFFFF"]Value 6[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FFFFFF"]SYSTEM1[/TD]
[TD="bgcolor: #FFFFFF"]Check3[/TD]
[TD="bgcolor: #FFFFFF"]Value 3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]System3[/TD]
[TD="bgcolor: #FFFFFF"]Value 7[/TD]
[TD="bgcolor: #FFFFFF"]Value 8[/TD]
[TD="bgcolor: #FFFFFF"]Value 9[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FFFFFF"]SYSTEM2[/TD]
[TD="bgcolor: #FFFFFF"]Check1[/TD]
[TD="bgcolor: #FFFFFF"]Value 4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FFFFFF"]SYSTEM2[/TD]
[TD="bgcolor: #FFFFFF"]Check2[/TD]
[TD="bgcolor: #FFFFFF"]Value 5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FFFFFF"]SYSTEM2[/TD]
[TD="bgcolor: #FFFFFF"]Check3[/TD]
[TD="bgcolor: #FFFFFF"]Value 6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FFFFFF"]SYSTEM3[/TD]
[TD="bgcolor: #FFFFFF"]Check1[/TD]
[TD="bgcolor: #FFFFFF"]Value 7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FFFFFF"]SYSTEM3[/TD]
[TD="bgcolor: #FFFFFF"]Check2[/TD]
[TD="bgcolor: #FFFFFF"]Value 8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FFFFFF"]SYSTEM3[/TD]
[TD="bgcolor: #FFFFFF"]Check3[/TD]
[TD="bgcolor: #FFFFFF"]Value 9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: #FFFFFF"]SYSTEM4[/TD]
[TD="bgcolor: #FFFFFF"]Check1[/TD]
[TD="bgcolor: #FFFFFF"]Value 10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F18[/TH]
[TD="align: left"]{=INDEX($C$18:$C$27,SMALL(IF($A$18:$A$27=$E18,IF($B$18:$B$27=F$17,ROW($B$18:$B$27)-ROW($B$18)+1)),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[jamtay317]

f18 was coppied down and accross

 

[idkbrits]

Works perfectly, thank you very much.

Does anyone know if there is a tutorial for extreme noobs with regards to working with arrays, even in my studying days when I was doing coding, arrays where my downfall.

Think it's time to revisit...

Thanks again!!!

 

[jamtay317]

Mike Girvin wrote a book on Arrays and it will come out soon, I'm sure that will be more of what your wanting... I learned a lot of things from his first book.You may want to check that out. I think that it comes out in June