Index match multiple reults

[MrWoods1990]

Good evening Forum!

In the attached workbook I am currently running a INDEX MATCH forumla (Massive thanks to FlameRetired for helping me out with that one)

I now need to develop the formula if possible to return multiple results, for example.

Sheet "X1" is my main database of information, Sheet "Look Up Search" is my search feature.

When I enter the reference number in H4 it rightly returns the first instance of this reference number from Sheet "X1" and next four records after it.

I would like to amend the formula to find all instances of "1 Of 27/11/17" from sheet X1 and return them in the "Look Up Search" so the information column reads "Alpha, Bravo, Charlie, Delta" in order.

I'm afraid INDEX MATCH is just too complicated for me to understand so I am asking if this is possible?

Kind regards.

[FlameRetired]

MrWoods1990 I am confused.

I vaguely recall this. The construction and apparent intent of the formula in X1 column A is to return unique values ... "1 of 27/11/17" for instance. With that understanding there won't be multiple "1 of 27/11/17".

What am I missing?

Edit An additional point. Merged cells in source data ('X1') cause havoc for formulas. They should be avoided.

The best solution there is to eliminate the merging and fill those ranges with their respective Alpha, Bravo, Charlie and Delta or set up a helper column in sheet 'X1' that fulfills the same purpose.

Without one or the other of those changes any formula solution is going to be excessively complicated, long and a nightmare to edit, analyze, debug and maintain.

Can either of those things be done?

[MrWoods1990]

MrWoods1990 I am confused.

I vaguely recall this. The construction and apparent intent of the formula in X1 column A is to return unique values ... "1 of 27/11/17" for instance. With that understanding there won't be multiple "1 of 27/11/17".

What am I missing?

Apologies,

I did miss something.

I not only use the forumla in this context but in another context that works in the same way but searches for a name instead of a reference number.

Imagine "1 of 27/11/17" being "John Smith" for example.

I would like to return all instances as there could well be multiples.

I hope this helps.

[FlameRetired]

Thank you.

My "Edit" crossed in the ether. Have you seen my notes RE: merged data source cells?

[MrWoods1990]

Thank you.

My "Edit" crossed in the ether. Have you seen my notes RE: merged data source cells?

I have un-merged the cells and uploaded a new Sample. They do not need to be merged for what I am trying to achieve.

[FlameRetired]

If I now understand correctly try this in G9 of 'Look Up Search' and fill down.

Please Login or Register  to view this content.

[MrWoods1990]

If I now understand correctly try this in G9 of 'Look Up Search' and fill down.

Please Login or Register  to view this content.

Along the right lines but is there a way to replicate these results across every cell?

So as a result of entering "1 of 27/11/17" in H4 I would like cells A9, A13, A17 and A21 to Display "1 of 27/11/17" and cells G9 to display Alpha, G13 to display Bravo, G17 to display Charlie and G21 to display Delta?

If it makes it easier then view every 4 lines in sheet X1 as one record with the reference number to each in the top left of every four lines, so when I type in "1 of 27/11/17" in H4 it searches X1 and returns all four records with that reference number.

[FlameRetired]

I kept looking for a simpler way to do this. Right now I can’t see through the "brush piles".

Array enter this in A9 fill down and across column F. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Enter this in G9 the regular way and fill down. I re-merged the output cells.

Please Login or Register  to view this content.

[FlameRetired]

I over complicated the previous formula in A9. In fact you can even re-merge the cells in column G of X1 if necessary. Although it is almost always bad practice to have merged cells in source data you can get away with it in this case.

None of these have to be array entered.
In A9 filled down and across column F.

Please Login or Register  to view this content.

In G9 and filled down.

Please Login or Register  to view this content.

[MrWoods1990]

Dave,

As always.

I am baffled as to how you manged it but this is exactly what I am looking for and I can now replicate the results in my live version.

With greatest thanks.

[FlameRetired]

You're welcome. Glad to help. Thanks for the feedback, added rep and marking your thread Solved.