Matching multiple times WITHOUT array formulas.

There are two other ways that I can think of - the first would use a helper column to identify matches and allocate a unique sequential number to each match, and the second would be to adjust the range after each match and use INDIRECT (although that is a volatile function and could cause performance problems).

Hope this helps.

Pete

See the attachment.

Did you see my attachment on Post#4? This is with that helper column.

Also you can use another helper method. See this attachment.

Originally Posted by Tony Valko

Instead of hiding your suggestion in a file why don't you put it directly in the post where we can see it?

Because this help people to understand well.

On helper column I used that formula

Please Login or Register  to view this content.

On the output I used that formula

Please Login or Register  to view this content.

But they will make complete sense if you see the file I think. May be I think wrong.

Originally Posted by Pete_UK

However, if you want to have two or more items shown in the lower table, then the helper column needs to include something else to help identify the type of record, as well as the unique number. In this case I would use this in C2:

=A2&"_"&COUNTIF(A$2:A2,A2)

and copy down, and this formula would give Dog_1, Dog_2, Cat_1, Dog_3, and so on. Then in B15 you would have this:

=INDEX($B$1:$B$10,MATCH($A15&"_"&COLUMNS($B:B),$C$1:$C$10,0)),"")

which can be copied across AND down to complete the lower table.

As you say, it is the helper column that does all the work, and the expressions in there can become quite complex - you might want to compare several columns of data, for example, so you would have an AND function - the crucial thing is that you are setting up some unique reference for each record, and then in your summary table you need to re-create those unique references in the MATCH function.

Hope this helps.

Pete

Awesome, that's basically what I was thinking, but I was trying to go about it wrong. You make things look so easy. However, I think you forgot the IF part of this formula:

=INDEX($B$1:$B$10,MATCH($A15&"_"&COLUMNS($B:B),$C$1:$C$10,0)),"")

I believe I know where you were going with it, but I'd still like to see if your thought process is easier than mine.

Here my helper column is C.

Column F contains all ID's from row 5. Row 5 contains matched number from column G to J.

Then I used the following formula in G5 and copy them to other output cells.

Please Login or Register  to view this content.

Originally Posted by sanram

Because this help people to understand well.

On helper column I used that formula

Please Login or Register  to view this content.

On the output I used that formula

Please Login or Register  to view this content.

But they will make complete sense if you see the file I think. May be I think wrong.

Nope, your file accomplishes the same thing I wanted now. I didn't quite understand the first one, but this one makes more sense to me.

Thanks to both of you, been a great help, I can tweak these to work with my real file now, thanks.

Great! It sounds good now that you got the solution.

I've attached the file based on the second set of formulae that I gave you.

Hope this helps.

Pete

Sorry, forgot to attach it.

Pete

EDIT: And yes, I forgot the IFERROR in the earlier post (corrected now).

Originally Posted by Pete_UK

Sorry, forgot to attach it.

Pete

EDIT: And yes, I forgot the IFERROR in the earlier post (corrected now).

That's really a good solution by using columns().

ah iferror(), I always forget about that one and go for if or if(iserror()).

Thanks.

Originally Posted by FDibbins

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

Yes. I understand and always try to give my solution directly here. But sometime I couldn't express myself without a sample.

Just for completeness, I've put a file together to show how you can use INDIRECT to get multiple matches. This approach does not need a helper column, so I just have the same data as before in A1:B10, with "Dog" in A15. This formula in B15 will get the first record that matches:

=IFERROR(INDEX($B$2:$B$10,MATCH($A15,$A$2:$A$10,0)),"")

and it can be copied down for the other categories, but it can't be copied across as it will always return the first match. Instead, I've used this formula in C15:

=IFERROR(INDEX(INDIRECT("B"&MATCH(B15,$B$1:$B$10,0)+1&":B10"),MATCH($A15,INDIRECT("A"&MATCH(B15,$B$1:$B$10,0)+1&":A10"),0)),"")

and this CAN be copied across and down to pick up the subsequent matching records. It works by adjusting the ranges that are being searched, so as to exclude the rows where we have already found a match.

Hope this helps.

Pete

Cool, never knew that was even possible.

I'll stick to the helper column method, as it makes more sense to me, but that is quite the impressive formula there.

Originally Posted by Pete_UK

Just for completeness, I've put a file together to show how you can use INDIRECT to get multiple matches. This approach does not need a helper column, so I just have the same data as before in A1:B10, with "Dog" in A15. This formula in B15 will get the first record that matches:

=IFERROR(INDEX($B$2:$B$10,MATCH($A15,$A$2:$A$10,0)),"")

and it can be copied down for the other categories, but it can't be copied across as it will always return the first match. Instead, I've used this formula in C15:

=IFERROR(INDEX(INDIRECT("B"&MATCH(B15,$B$1:$B$10,0)+1&":B10"),MATCH($A15,INDIRECT("A"&MATCH(B15,$B$1:$B$10,0)+1&":A10"),0)),"")

and this CAN be copied across and down to pick up the subsequent matching records. It works by adjusting the ranges that are being searched, so as to exclude the rows where we have already found a match.

Hope this helps.

Pete

This is a great method, however how can this apply across multiple worksheets (for external data source)? I've tried this, but it doesn't seem to cross reference correctly:

=IFERROR(INDEX(INDIRECT("'new.xls'!B"&MATCH(B15,'new.xls'!$B$1:$B$10,0)+1&":B10"),MATCH($A15,INDIRECT("'new.xls'!A"&MATCH(B15,'new.xls'!$B$1:$B$10,0)+1&":A10"),0)),"")

Hi Pete_UK,
It is a great formula and i was going to use it in my document but unfortunally i run up to a problem. The formula can't handle 2 times the same output. For example, if there are 2 same dogs, the results after this for all other dogs become all the same.. That was a disapointment. Any way to solve this?

You should not try to hijack someone else's thread (see the Forum Rules at the top of the screen). Start your own new thread, with a link back to this if you think it is relevant.

Pete