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

 

[avn80]

Are you saying that DDD should not peered with A as well as with B?

Yes , that would be an ideal solution.

 

[Aladin Akyurek]

Are you saying that DDD should not peered with A as well as with B?

Yes , that would be an ideal solution.

Sheet2, A:B, houses the target companies with the associated market caps...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Company[/TD]
[TD]Market Cap[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]AAA[/TD]
[TD]

170000​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]BBB[/TD]
[TD]

20000​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]CCC[/TD]
[TD]

40000​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]DDD[/TD]
[TD]

2000​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]EEE[/TD]
[TD]

30000​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]FFF[/TD]
[TD]

1000​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]GGG[/TD]
[TD]

60000​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]HHH[/TD]
[TD]

70000​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]III[/TD]
[TD]

19000​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]JJJ[/TD]
[TD]

29000​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]KKK[/TD]
[TD]

30000​

[/TD]
[/TR]
</tbody>[/TABLE]


Select A2:A12, name the selection Tcompanies (from target companies) via the Name Box or Formulas | Name Manager.

In the same way, select B2:B12 and name the selection Tmcaps.

Define Ivec using Formulas | Name Manager as referring to:

=ROW(Tcompanies)-ROW(INDEX(Tcompanies,1,1))+1

Sheet1, where the processing takes place...

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

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

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

1​

[/td][td]Company[/td][td]Mkt.Cap[/td][td]

30%​

[/td][td]

-30%​

[/td][td]Peer companies[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]A[/td][td]

10000​

[/td][td]

13000​

[/td][td]

7000​

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

[tr][td]

3​

[/td][td]B[/td][td]

20000​

[/td][td]

26000​

[/td][td]

14000​

[/td][td]BBB[/td][td]III[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

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

130000​

[/td][td]

169000​

[/td][td]

91000​

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

[tr][td]

5​

[/td][td]D[/td][td]

20000​

[/td][td]

26000​

[/td][td]

14000​

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

[tr][td]

6​

[/td][td]E[/td][td]

50000​

[/td][td]

65000​

[/td][td]

35000​

[/td][td]CCC[/td][td]GGG[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]F[/td][td]

30000​

[/td][td]

39000​

[/td][td]

21000​

[/td][td]EEE[/td][td]JJJ[/td][td]KKK[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]G[/td][td]

40000​

[/td][td]

52000​

[/td][td]

28000​

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

E2, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Tcompanies,SMALL(IF(FREQUENCY(IF(Tmcaps>=$D2,
   IF(Tmcaps<=$C2,IF(Tcompanies<>"",
   IF(COUNTIFS($E$1:INDEX($E$1:$Z1,0,ROWS(Tcompanies)),Tcompanies)=0,
   MATCH(Tcompanies,Tcompanies,0))))),Ivec),Ivec),COLUMNS($E2:E2))),"")

 

[jonothanbird]

Hi Aladin, I apologize for hijacking this thread but I have a very similar issue and would be extremely grateful if you pointed me in the right direction.

I have a large database on a shared network, and another workbook which acts as a GUI of sorts for inputting information. I am trying to figure out a way to create a searchbox which will pull relevant information from the database and present it on the GUI. I learned a little VBA making this, but am a novice when it comes to excel formulas. From what I understand this is possible with a similar formula (or the same) as what you have posted in this thread. The information on the database looks like

Client | Contact | Title | Phone # | Cell # | Email | Address | City | State | Zip | Country | Remarks
Microsoft | Bill gates | CEO | xxx-xxx-xxx | xxx-xxx-xxx| xxx@xxx.com |(Address)|(city)|(State)| (Zip) | (country) | (remarks)
ect
ect
ect
ect


The term to search with would be "client" and "contact" (changed with option button). There are duplicate results for many of the search terms, I need them all to display. I have dynamic named ranges in the database I want the formulas to reference. Any advice would be deeply appreciated.

Thanks

 

[avn80]

Sheet2, A:B, houses the target companies with the associated market caps...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Company[/TD]
[TD]Market Cap[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]AAA[/TD]
[TD]

170000​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]BBB[/TD]
[TD]

20000​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]CCC[/TD]
[TD]

40000​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]DDD[/TD]
[TD]

2000​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]EEE[/TD]
[TD]

30000​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]FFF[/TD]
[TD]

1000​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]GGG[/TD]
[TD]

60000​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]HHH[/TD]
[TD]

70000​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]III[/TD]
[TD]

19000​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]JJJ[/TD]
[TD]

29000​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]KKK[/TD]
[TD]

30000​

[/TD]
[/TR]
</tbody>[/TABLE]


Select A2:A12, name the selection Tcompanies (from target companies) via the Name Box or Formulas | Name Manager.

In the same way, select B2:B12 and name the selection Tmcaps.

Define Ivec using Formulas | Name Manager as referring to:

=ROW(Tcompanies)-ROW(INDEX(Tcompanies,1,1))+1

Sheet1, where the processing takes place...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Company[/TD]
[TD]Mkt.Cap[/TD]
[TD]

30%​

[/TD]
[TD]

-30%​

[/TD]
[TD]Peer companies[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]A[/TD]
[TD]

10000​

[/TD]
[TD]

13000​

[/TD]
[TD]

7000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]B[/TD]
[TD]

20000​

[/TD]
[TD]

26000​

[/TD]
[TD]

14000​

[/TD]
[TD]BBB[/TD]
[TD]III[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]C[/TD]
[TD]

130000​

[/TD]
[TD]

169000​

[/TD]
[TD]

91000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]D[/TD]
[TD]

20000​

[/TD]
[TD]

26000​

[/TD]
[TD]

14000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]E[/TD]
[TD]

50000​

[/TD]
[TD]

65000​

[/TD]
[TD]

35000​

[/TD]
[TD]CCC[/TD]
[TD]GGG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]F[/TD]
[TD]

30000​

[/TD]
[TD]

39000​

[/TD]
[TD]

21000​

[/TD]
[TD]EEE[/TD]
[TD]JJJ[/TD]
[TD]KKK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]G[/TD]
[TD]

40000​

[/TD]
[TD]

52000​

[/TD]
[TD]

28000​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


E2, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Tcompanies,SMALL(IF(FREQUENCY(IF(Tmcaps>=$D2,
   IF(Tmcaps<=$C2,IF(Tcompanies<>"",
   IF(COUNTIFS($E$1:INDEX($E$1:$Z1,0,ROWS(Tcompanies)),Tcompanies)=0,
   MATCH(Tcompanies,Tcompanies,0))))),Ivec),Ivec),COLUMNS($E2:E2))),"")

Thanks a ton. Though it dint come out as I expected initially, I managed to play with it to reach some steps closer than where I initially was. But could you please elaborate on Ivec? Could not find much online either. Is it something that you just defined or is it a function of excel?

 

[Aladin Akyurek]

Thanks a ton. Though it dint come out as I expected initially, I managed to play with it to reach some steps closer than where I initially was. But could you please elaborate on Ivec? Could not find much online either. Is it something that you just defined or is it a function of excel?

Did you run the instructions as given for your rather vague feedback implies that it doesn't somehow fit what you have specified?

 

[labKK]

Hello,

I hope you still watch this thread Aladin. You are amazing.
I want to ask you something which is related with the first example in first page.
Is there a way to search in a array without using the exact string?

eg. if I use "Mous" instead of "Mouse" for the lookup word, I want to get the same results: Mickey and Minnie

It would be great if you had an answer for this.

 

[Aladin Akyurek]

Hello,

I hope you still watch this thread Aladin. You are amazing.
I want to ask you something which is related with the first example in first page.
Is there a way to search in a array without using the exact string?

eg. if I use "Mous" instead of "Mouse" for the lookup word, I want to get the same results: Mickey and Minnie

It would be great if you had an answer for this.

Care to specify which formula are you referring to?

 

[labKK]

Yes, the code is in your post #4.
I am trying to replace the "$A$2:$A$8=$D2"part of your code with a search function like SEARCH($D2,$A$2:$A$8).

Of course I wouldn't mind if you know a way to do it without the SEARCH function

My goal is to make this to work without using the exact string in D2.

 

[Aladin Akyurek]

Yes, the code is in your post #4.
I am trying to replace the "$A$2:$A$8=$D2"part of your code with a search function like SEARCH($D2,$A$2:$A$8).

Of course I wouldn't mind if you know a way to do it without the SEARCH function

My goal is to make this to work without using the exact string in D2.

Some options...

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

=IFERROR(INDEX($B$2:$B$8,SMALL(IF(LEFT($A$2:$A$8,LEN($D2))=$D2,
  ROW($B$2:$B$8)-ROW($D$2)+1),  COLUMNS($E2:E2))),"")<strike>
</strike>

Does one of these help?

 

[labKK]

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