Displaying multiple results from an Index Formula

[charmy4]

Hello,

I have 3 columns of data and I want to display multiple cells from column C that satsify criteria in both Column A and Column B. I have put together a formula (below) which works well but only returns one result.

=INDEX($C$2:$C$647,MATCH(1,($A$2:$A$647="criteria 1")*($B$2:$B$647="criteria 2"),0)) and confirm with ctrl+****+enter

How do I do this so it returns all the results that satisfy the required criteria?

Thank you in advance!

[Bob Phillips]

Use

Please Login or Register  to view this content.

[Pete_UK]

Post an example workbook showing exactly what you want to achieve. INDEX will only return one value, and you may want to concatenate the values together in one cell or have them showing in adjacent cells, but either way you will need more than just that formula.

Pete

[charmy4]

Attached is the spreadsheet. Apologies, I've had to alter it as the info was confidential.

I was hoping to pull the 'client names' from column C that fall into each 'sub-group' in column B for each 'group' in column A and display these results in the table on sheet 2.

The reason I can't just paste a link is that the criteria are likely to change.

Thank you!

Excel Help sample.xls

[charmy4]

Thanks, but this only seems to work if it is in a cell on the same sheet and row of the result.

[Bob Phillips]

Are you referring to my answer? If so, that is not correct.

[charmy4]

Hi Bob,

Yes I'm afraid it doesn't seem to work unless it is in the same row as the solution

To clarify I'm looking to show all results from column C on a different sheet that satisfy one criteria in column A and another in column B.

For example: show all client names in col. C that are part of 'Group D' (col. A) and sub-group 'E' (col. B).

Thanks!

[Pete_UK]

In the attached file I have made use of two helper columns in the Client_List sheet, to join the Group and sub group together and then to get a unique sequential reference for each entry.

Then in the Table sheet I have put this formula in C3:

Please Login or Register  to view this content.

This can then be copied across to column I and then C3:I3 copied down to row 10 to give you what you want for Group A.

I've also set up 3 identical tables lower down on the sheet to get the same results for the other groups - the formulae are slightly different in each table, as they refer to the group in the top cell of each table.

As an alternative, I've also set up data validation in cell A1 to enable you to choose the group from a drop-down, and the top-table will automatically adjust. If you decide to use this, then you can delete the lower tables.

Hope this helps.

Pete

[charmy4]

Thanks Pete. Am currently implementing it to my data but seems to be working. Very well done.

Thanks again!

[Cutter]

@ charmy4

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.