Index Match data to provide multiple results in columns

[lomond44]

I need some help with a spreadsheet that contains 2 worksheets.

The first sheet contains part numbers and the assemblies that they are used in. Each part number can be used in many assemblies.

On the 2nd sheet is a list of just the part numbers without duplicates. The aim is to create columns to the right of each part number that identifies all the assemblies that the part is used in.

I have looked at this link How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP | eImagine Technology Group but I do not want the results to be down a column but to go across the row.

Any help appreciated

 

[Aladin Akyurek]

Hey guys, I was using this formula to handle imported date for my job. Everything about this formula works amazingly.

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2))

You need to robustify this though and you can replace IF(ISERROR bit with IFERROR for more speed...

=IFERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)-ROW($A$1)+1),ROWS($1:1)),2),"")

​

However, I'm running to a little issue. I need for this formula to look up values/data from the first two columns as opposed of just A1:A8. Can anyone help me out with this?

[…]

I trying to get the "Lookup: Mouse" To lookup not just values from Column A but also Column B. Like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Animal 1[/TD]
[TD]Animal 2[/TD]
[TD]Items[/TD]
[TD][/TD]
[TD]Lookup[/TD]
[TD]Mouse[/TD]
[/TR]
[TR]
[TD]Duck[/TD]
[TD]Mockingjay[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Matches[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Mouse[/TD]
[TD]Cat[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Dog[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Mouse[/TD]
[TD]Elephant[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lion[/TD]
[TD]Mouse[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In the last row, "Mouse" appears on Column B. Is there a way to make the formula read column B as well as column A? Also, I do not intent to have repeated values in a row. In case that makes things easier?

[…]

In F2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$2:$C$6,SMALL(IF($A$2:$A$6=$B$2:$B$6,"",IF($A$2:$B$6=F$1,ROW($C$2:$C$6)-ROW($C$2)+1)),ROWS($F$2:F2))),"")

If it's not possible to have duplicates, i.e. A = mouse and B = mouse, then:

=IFERROR(INDEX($C$2:$C$6,SMALL(IF($A$2:$B$6=F$1,ROW($C$2:$C$6)-ROW($C$2)+1),ROWS($F$2:F2))),"")

will suffice.

 

[SARABECK]

Hello,

I modify the formula provided in post #12 to use in my file.

The problem i'm running into is that on 5/30/18 there were 2 invoices issued (ex: 14000 and 14001) however, the formula picks up the first invoice twice and not the 2nd one.

How can I edit the formula to pickup the 2nd invoice too?

To get the invoice#


B24 =IFERROR(INDEX(REPORT1!$P$1:$P$1032,AGGREGATE(14,6,(ROW(REPORT1!$P$1:$P$1032)-ROW(REPORT1!$P$1)+1)/((REPORT1!$G$1:$G$1032=Sheet6!$A$22)*(REPORT1!$Y$1:$Y$1032=Sheet6!A24)),ROWS(1:1))),"")

B25 =IFERROR(INDEX(REPORT1!$P$1:$P$1032,AGGREGATE(14,6,(ROW(REPORT1!$P$1:$P$1032)-ROW(REPORT1!$P$1)+1)/((REPORT1!$G$1:$G$1032=Sheet6!$A$22)*(REPORT1!$Y$1:$Y$1032=Sheet6!A25)),ROWS(2:2))),"")

To get the QTY

C24 =IFERROR(INDEX(REPORT1!$U$1:$U$1032,AGGREGATE(14,6,(ROW(REPORT1!$U$1:$U$1032)-ROW(REPORT1!$U$1)+1)/((REPORT1!$G$1:$G$1032=Sheet6!$A$22)*(REPORT1!$Y$1:$Y$1032=Sheet6!A24)),ROWS(1:1))),"")

C25 =IFERROR(INDEX(REPORT1!$U$1:$U$1032,AGGREGATE(14,6,(ROW(REPORT1!$U$1:$U$1032)-ROW(REPORT1!$U$1)+1)/((REPORT1!$G$1:$G$1032=Sheet6!$A$22)*(REPORT1!$Y$1:$Y$1032=Sheet6!A25)),ROWS(2:2))),"")

The results are below:

Sheet 6
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]000723[/TD]
[TD]Invoice#[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]5/31/18[/TD]
[TD]13000[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]5/30/18[/TD]
[TD]14000[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]5/30/18[/TD]
[TD]14000[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]6/28/18[/TD]
[TD]18000[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance.

 

[Aladin Akyurek]

@ SARABECK

I modify the formula provided in post #12 to use in my file.

What are the values of

Sheet6!A24 and

Sheet6!A25?​

​

 

[SARABECK]

Thank you so much Aladin for replying.


In sheet 6 - A24 and A25 are the dates.
In sheet 6 - A22 is the product.


Here is my sample:

Sheet 6

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]ROW/COLUMN[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]000723[/TD]
[TD]Invoice#[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]5/31/18[/TD]
[TD]13000[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]5/30/18[/TD]
[TD]14000[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]5/30/18[/TD]
[TD]14000[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]6/28/18[/TD]
[TD]18000[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Thank you so much Aladin for replying.


In sheet 6 - A24 and A25 are the dates.
In sheet 6 - A22 is the product.


Here is my sample:

Sheet 6

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]ROW/COLUMN[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]000723[/TD]
[TD]Invoice#[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]5/31/18[/TD]
[TD]13000[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]5/30/18[/TD]
[TD]14000[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]5/30/18[/TD]
[TD]14000[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]6/28/18[/TD]
[TD]18000[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
</tbody>[/TABLE]

B24, copied down:

=IFERROR(INDEX(REPORT1!$P$1:$P$1032,AGGREGATE(14,6,(ROW(REPORT1!$P$1:$P$1032)-ROW(REPORT1!$P$1)+1)/((REPORT1!$G$1:$G$1032=$A$22)*(REPORT1!$Y$1:$Y$1032=A24)),COUNTIFS($A$24:A24,A24))),"")​

C24, copied down:

=IFERROR(INDEX(REPORT1!$U$1:$U$1032,AGGREGATE(14,6,(ROW(REPORT1!$U$1:$U$1032)-ROW(REPORT1!$U$1)+1)/((REPORT1!$G$1:$G$1032=$A$22)*(REPORT1!$Y$1:$Y$1032=A24)),COUNTIFS($A$24:A24,A24))),"")
​

 

[SARABECK]

That worked, thank you so much.

To understand this: You didn't need to add ROWS(1:1) in your formula? Why is that?

Thank you again.

 

[Aladin Akyurek]

That worked, thank you so much.

To understand this: You didn't need to add ROWS(1:1) in your formula? Why is that?

Thank you again.

ROWS($1:1) and COUNTIFS($A$24:A24,A24) are equivalent in your situation, but not ROWS(1:1).

The latter, i.e. ROWS(1:1), will always return 1 when the formula it's part of is copied down:

ROWS(1:1) >> 1, i.e. 1 row from row 1 to row 1
ROWS(2:2) >> 1, i.e. 1 row from row 2 to row 2

while the first two will give 1, then 2, etc.​

​