list all values that begin with a specific letter

I am able to use the following to list all values in a column (C2:C3177) of one spreadsheet that have the value (C6) in another spreadsheet - works very nicely

=INDEX(Sheet1!$C$2:$C$3177,SMALL(IF(Sheet1!$C$2:$C$3177=C6,ROW(Sheet1!$C$2:$C$3177)),ROW(1:1))-1,1)

What I'd like to do is list all the values in C2:C3177 that start with a specific letter - preferably this letter would be defined by the value in a cell (eg C6)

Many thanks Ali - should have spotted that's where I could add the Left function - keep up the great help! and Merry Christmas

Ah - whlist we're here - the next step would be to count the number of cells in the column that start with the value in C6

I can use =COUNTIF(Sheet1!C2:C3177,"c*") to find how many start with c but would ideally like the formula to be based on the value in C6 so I can change it as needed?

Oops - sorry - yes I suppose it is - that work's great - how's sunny Ipswich this morning - I'm up in windy Norwich

Unseasonably mild! Thanks again Ali

Hi, apologies for resurrecting an old thread, but... the OP states the scenario I am trying to solve (list all values that begin with a specific letter) but the formula provided by AliGW isn't working for me.

I have a long list of 'names' (multiple words and single words e.g. 'Me Too', 'Tubbies on Telly' or 'Footing') on a sheet. For context this is a logbook of rock climbs in different places.
On a different sheet I want to list all cells that start with a specific letter that is entered in cell M1.

I modified Ali's formula to use my sheets =INDEX('all boulders'!$D$12:$D$7263,SMALL(IF(LEFT('all boulders'!$D$12:$D$7263,1)=$M$1,ROW('all boulders'!$D$12:$D$7263)),ROW(1:1))-1,1)
but it returns #VALUE! unless I make it an array formula by doing ctrl+shift+enter, at which point it returns a valid name from the list but this name does not start with the letter entered in cell M1. e.g. M1 = "J", result given is "Thriving Sincerity".

Is there some I'm missing here?

I have been generating similar lists from this data, returning the names based on what 'place' I select from a dropdown box using this formula:
=IFERROR(INDEX('all boulders'!$D$12:$D$7642,AGGREGATE(15,6,(ROW('all boulders'!$B$12:$B$7642)-ROW('all boulders'!$B$12)+ 1)/('all boulders'!$B$12:$B$7642 =$H$1), ROW('all boulders'!$A1))), "")
The value in $H$1 is the selected 'place' that I want the list of names to be at. i.e. 'Tubbies on Telly' is at a place called 'Fontainebleau'.
I am able to drag this formula down until it returns '0' when there are no more names listed as being in Fontainebleau.

I thought I might be able to modify this formula to list all names beginning with a specific letter by using the LEFT formula instead of the AGGREGATE formula but I can't get it to work.

I confess I am not knowledgable with excel formula. Up to now I have found answers to my problems by searching the web and manipulating the suggested solutions I find. e.g. I can't explain to you/anyone how the above formula works exactly but I kind of see what it is doing!

Apologies for the long, potentially confusing, post. I hope one of you amazing peeps can help me out.
Regards,
rew

attached file, zipped.