Return array of values based on text

[djm601]

Hi,

I'm creating a holiday planner that has dates in one row from Jan-Dec and a list of names in column A.

I am trying to create a report on a separate sheet that returns all dates based on a the code entered in the rows beneath.

I'm currently using this:

=INDEX($I$9:$GH9,MATCH(B25,$I$10:$GH$10,0))

where B25 has the value of the fist code, in this case "H" (for Holiday)

This works great for the first entry of the letter "H" and returns the date above as expected.

Can someone point me in the direction to return all occurrences in the row that have a "H" below.

Your help is much appreciated.

[Fotis1991]

One way is using INDEX with SMALL(IF, as an array formula.

If this is accepted, pls upload a small sample workbook.

[djm601]

Thanks for the quick response.

I've added a sample attachment. The formula is in green on the sheet.

Cheers,

DAve

[Fotis1991]

There has to be a better solution, but once your data is in landscape mode, convert them to vertical and have the desired effect.

See the example.

[djm601]

Thank you - your help is much appreciated.

I was almost there when you mentioned SMALL, IF... but ran into the same problem with it being in landscape. Wasn't aware of the Transpose function, so I've learnt two things today.

Appreciate you help .