Index Match data to provide multiple results in columns

lomond44

New Member
Joined
May 6, 2014
Messages
7
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 :)
 
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

Animal 1Animal 2ItemsLookupMouse
DuckMockingjay3Matches12
MouseCat1214
CatDog49
MouseElephant14
LionMouse9

<tbody>
</tbody>


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.
 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
ABC
22000723Invoice#QTY
23
245/31/181300050
255/30/1814000100
265/30/1814000100
276/28/1818000200

<tbody>
</tbody>

Thank you in advance.
 
Upvote 0
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

ROW/COLUMNABC
22000723Invoice#QTY
23
245/31/181300050
255/30/1814000100
265/30/1814000100
276/28/1818000200

<tbody>
</tbody>
 
Last edited:
Upvote 0
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

ROW/COLUMNABC
22000723Invoice#QTY
23
245/31/181300050
255/30/1814000100
265/30/1814000100
276/28/1818000200

<tbody>
</tbody>

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))),"")
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top