How to get records based on a condition using Index Match?

[Eduards]

I have this table:

Please Login or Register  to view this content.

I am aiming to get all areas and items that are monthly, here is what I have tried:

Please Login or Register  to view this content.

But this returns me this result:

Please Login or Register  to view this content.

How can I return each area that is a monthly frequency and on the next column, display it's item also.

[Pete_UK]

You can use this formula in D2:

=IF(C2=$G$1,MAX(D$1:D1)+1,"-")

to identify appropriate records with a sequential number. Copy down to the bottom of your data. Then you can use this formula in F2:

=IFERROR(INDEX($A$2:$A$16, MATCH(ROWS($1:1),$D$2:$D$16,0))&" - "&INDEX($B$2:$B$16, MATCH(ROWS($1:1),$D$2:$D$16,0)),"")

to bring the Area and the Item across. Copy down as required. Change the entry in G1 to see other lists.

Hope this helps.

Pete

[FlameRetired]

Another way. Not sure I've interpreted correctly.

In G2 filled down and across until you get blanks.

Please Login or Register  to view this content.

	G	H
1	Monthly
2	Area1	Item1
3	Area1	Item2
4	Area1	Item4
5	Area1	Item5
6	Area3	Item2
7	Area3	Item3
8
9
10
11
12
13
14
15
16

[Eduards]

Thank you for that, this works as expected.

I should of been more cleared with my question..

Because I am actually going to have another 2 sheets, named "Weekly" and "Monthly". There I'll be wanting to get it's relevant values.

[kvsrinivasamurthy]

In G2 then copied across and down.

Please Login or Register  to view this content.