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]

Hello Aladin,

The first code did the job for me.
The only problem is that if you have an empty cell for a "lookup" word, it returns all cells as true. I guess this is happening because SEARCH function finds " " in all my data and always returns TRUE.
But I am fine with this. I can fill the "lookup column" with some special characters and avoid empty cells.
Anyway, great job as always. Thank you very much.

For the record, the second code does not work if your lookup string does not start with the string in the search array.
e.g. "Mou" will return the expected results Mickey, Minnie but "ous" or "use" will not return anything

Control+shif+enter:

=IFERROR(INDEX($B$2:$B$8,SMALL(IF(ISNUMBER(SEARCH(IF($D2="","##",$D2),
   $A$2:$A$8)),ROW($B$2:$B$8)-ROW($D$2)+1),COLUMNS($E2:E2))),"")

 

[labKK]

Aladin your addition worked perfectly.
Thanks again for your time and effort.

 

[Aladin Akyurek]

Aladin your addition worked perfectly.
Thanks again for your time and effort.

Glad to help. Thanks for the update.

 

[Vulfen]

Hey I'm looking for some help in one similar to this however I have several variables I have to build in.
Here's the way my data is layed out

Column A is a customer name (what im searching for)
Column B is a Reference number (what i want to return)
Column C is a date of booking

What i need is to be able to search for a customer and return a list of the reference numbers, and to only return those that were booked between the date range.

Some of the customers in column A could have the same leters or first part of thier name, so i am looking for it to be an exact match to the one im looking for.
The customer i am looking for is always held in Cell B1 and the Date range is held B2 for Start date and C2 for the end date.

I have only provided the 3 columns above but i actually need to do this so it will return several results column B will change however it will always be looking for the same data in column A & C

Would appreciate any help that can be given.
Thanks

 

[Aladin Akyurek]

@Vulfen

Could you post a small sample along with the desired results?

 

[Vulfen]

Here's a Google doc with some basic data, 2 tabs one hold data the other is my desired results feel free to edit as required.

https://docs.google.com/spreadsheets/d/1Hs8BDYBNAncXGZpzkgkMANqMdgZzG-CjRzMOXQnu9cM/edit?usp=sharing

Thanks
Vulfen

 

[Aladin Akyurek]

Here's a Google doc with some basic data, 2 tabs one hold data the other is my desired results feel free to edit as required.

https://docs.google.com/spreadsheets/d/1Hs8BDYBNAncXGZpzkgkMANqMdgZzG-CjRzMOXQnu9cM/edit?usp=sharing

Thanks
Vulfen

It would be more convenient to have the sample data directly on the forum. Try to post an Excel readable, small sample, using one of the methods listed in the following: Attachments or using this add-in:https://app.box.com/s/soezox25h3w0q5s4rcyl instead of providing an outside link to a file.

 

[Vulfen]

Of so i think ive done this right this would be the kind of data id be looking at.

*	A	B	C	D
Customer Name	Reference Number	Booking Date	Quantity
Customer1	00888111C
Customer1	00888112C
Customer3	00888113C
Customer1	00888114C
Customer2	00888115C
Customer1	00888116C
Customer1	00888116C
Customer2	00888117C
Customer2	00888117C
Customer2	00888117C
Customer2	00888117C
Customer3	00888118C
Customer3	00888118C
Customer3	00888118C
Customer1	00888119C
Customer1	00888120C

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]01/12/2015[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]01/12/2015[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]01/12/2015[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]01/12/2015[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]02/12/2015[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]02/12/2015[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]02/12/2015[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]07/10/2015[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]07/10/2015[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]07/10/2015[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]07/10/2015[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]07/10/2015[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]07/10/2015[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]07/10/2015[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]10/12/2015[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]10/12/2015[/TD]
[TD="align: right"]4[/TD]

</tbody>

Excel tables to the web - Excel Jeanie Html 4


And This would be the desired result i would like.
The customer lookup must be exact match ups only
It is possible for a reference number to appear more than once and they have to appear in the results table on separate lines.
Currently i am just doing this by filtering and copy paste each customer, for around 100 customers #ballache

*	A	B	C	D	E
Customer Name	Customer1	*	*	*
Date Range	(date range included these dates)	*
*	*	*	*	*
Reference Number	Quantity	Date Booked	*	*
00888111C	*	*
00888112C	*	*
00888114C	*	*
00888116C	*	*
00888116C	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]01/12/2015[/TD]
[TD="align: right"]07/12/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]01/12/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]01/12/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]01/12/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]02/12/2015[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]02/12/2015[/TD]

</tbody>

Excel tables to the web - Excel Jeanie Html 4

 

[Aladin Akyurek]

Of so i think ive done this right this would be the kind of data id be looking at.

[...]

Exactly right...

And This would be the desired result i would like.
The customer lookup must be exact match ups only
It is possible for a reference number to appear more than once and they have to appear in the results table on separate lines.
Currently i am just doing this by filtering and copy paste each customer, for around 100 customers #ballache

That too. Thanks for providing the right type sample exhibits.

Sheet1

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Customer Name[/td][td]Reference Number[/td][td]Booking Date[/td][td]Quantity[/td][/tr]


[tr][td]

2​

[/td][td]Customer1[/td][td]00888111C[/td][td]

1/12/2015​

[/td][td]

1​

[/td][/tr]


[tr][td]

3​

[/td][td]Customer1[/td][td]00888112C[/td][td]

1/12/2015​

[/td][td]

3​

[/td][/tr]


[tr][td]

4​

[/td][td]Customer3[/td][td]00888113C[/td][td]

1/12/2015​

[/td][td]

1​

[/td][/tr]


[tr][td]

5​

[/td][td]Customer1[/td][td]00888114C[/td][td]

1/12/2015​

[/td][td]

2​

[/td][/tr]


[tr][td]

6​

[/td][td]Customer2[/td][td]00888115C[/td][td]

2/12/2015​

[/td][td]

1​

[/td][/tr]


[tr][td]

7​

[/td][td]Customer1[/td][td]00888116C[/td][td]

2/12/2015​

[/td][td]

1​

[/td][/tr]


[tr][td]

8​

[/td][td]Customer1[/td][td]00888116C[/td][td]

2/12/2015​

[/td][td]

1​

[/td][/tr]


[tr][td]

9​

[/td][td]Customer2[/td][td]00888117C[/td][td]

7/10/2015​

[/td][td]

4​

[/td][/tr]


[tr][td]

10​

[/td][td]Customer2[/td][td]00888117C[/td][td]

7/10/2015​

[/td][td]

4​

[/td][/tr]


[tr][td]

11​

[/td][td]Customer2[/td][td]00888117C[/td][td]

7/10/2015​

[/td][td]

4​

[/td][/tr]


[tr][td]

12​

[/td][td]Customer2[/td][td]00888117C[/td][td]

7/10/2015​

[/td][td]

1​

[/td][/tr]


[tr][td]

13​

[/td][td]Customer3[/td][td]00888118C[/td][td]

7/10/2015​

[/td][td]

2​

[/td][/tr]


[tr][td]

14​

[/td][td]Customer3[/td][td]00888118C[/td][td]

7/10/2015​

[/td][td]

1​

[/td][/tr]


[tr][td]

15​

[/td][td]Customer3[/td][td]00888118C[/td][td]

7/10/2015​

[/td][td]

1​

[/td][/tr]


[tr][td]

16​

[/td][td]Customer1[/td][td]00888119C[/td][td]

10/12/2015​

[/td][td]

3​

[/td][/tr]


[tr][td]

17​

[/td][td]Customer1[/td][td]00888120C[/td][td]

10/12/2015​

[/td][td]

4​

[/td][/tr]
[/table]

Sheet2

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Customer Name[/td][td]Customer1[/td][td]

5​

[/td][td][/td][/tr]


[tr][td]

2​

[/td][td]Date Range[/td][td]

1/12/2015​

[/td][td]

7/12/2015​

[/td][td][/td][/tr]


[tr][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td]Reference Number[/td][td]Quantity[/td][td]Booking Date[/td][td]Idx[/td][/tr]


[tr][td]

5​

[/td][td]00888111C[/td][td]

1​

[/td][td]

1/12/2015​

[/td][td]

2​

[/td][/tr]


[tr][td]

6​

[/td][td]00888112C[/td][td]

3​

[/td][td]

1/12/2015​

[/td][td]

3​

[/td][/tr]


[tr][td]

7​

[/td][td]00888114C[/td][td]

2​

[/td][td]

1/12/2015​

[/td][td]

5​

[/td][/tr]


[tr][td]

8​

[/td][td]00888116C[/td][td]

1​

[/td][td]

2/12/2015​

[/td][td]

7​

[/td][/tr]


[tr][td]

9​

[/td][td]00888116C[/td][td]

1​

[/td][td]

2/12/2015​

[/td][td]

8​

[/td][/tr]


[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In C1 just enter:

=COUNTIFS(Sheet1!A2:A17,B1,Sheet1!C2:C17,">="&B2,Sheet1!C2:C17,"<="&C2)<strike></strike>

In A5 just enter, copy across to C5, and copy down:

=IF($D5="","",INDEX(Sheet1!$A$1:$D$17,$D5,
  MATCH(A$4,INDEX(Sheet1!$A$1:$D$17,1,0),0)))<strike></strike>

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

=IF(ROWS($D$5:D5)<=$C$1,SMALL(IF(Sheet1!$A$1:$A$17=$B$1,
  IF(Sheet1!$C$1:$C$17>=$B$2,IF(Sheet1!$C$1:$C$17<=$C$2,
  ROW(Sheet1!$A$1:$A$17)-ROW(Sheet1!$A$1)+1))),ROWS($D$5:D5)),"")<strike></strike>

 

[Vulfen]

Thanks this has been a great help.