# Find all cells starting with "SS" - Array Formula with Partial matches????

#### tom.walford

##### New Member
Hi there,

I am trying to retrieve all three of the cells in a range that start with the letters SS (Study Session). The sessions are numbered SS01 to SS30 in a much bigger sheet.

 A B C D E F G H I J K L M N 1 NAME Num Study Num Careers Study 1 Study 2 Study 3 Mon 1 Mon 2 Mon 3 Mon 4 Mon 5 Mon 6 Mon 7 Mon 8 2 Student 3 1 SS01 SS02 SS09 12A/Ma1 12AMa/1 SS01 SS02 12C/Ph1a 12C/Ph1a C SS039

<tbody>
</tbody>

Let's assume that this particular student's three study periods are on Monday (period 3,4 and 8) I would want D2, E2 and F2 to say SS01, SS02 and SS09 respectively. The rest of the timetable goes from Mon 9 through to Friday 9 in a very large worksheet. Each students study sessions are at different times with them picking up three of the sessions from the thirty available.
I have already got the counter functions in B2 and C2 to work, but cannot get the sessions to copy into the three columns to the left of the table.

I assume that this is going to need some sort of array function, but my brain is not on the same wavelength as Excel today - any help would be very much appreciated.

Many thanks

Tom.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Fluff

##### MrExcel MVP, Moderator
How about in D2 & dragged right
=INDEX(\$G2:\$N2,AGGREGATE(15,6,(COLUMN(\$G2:\$N2)-COLUMN(\$G2)+1)/(LEFT(\$G2:\$N2,2)="SS"),COLUMNS(\$A\$1:A\$1)))

#### tom.walford

##### New Member
You are a legend!

Many thanks

Tom.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback