VLOOKUP with multiple matches

I have a fairly complicated problem that I'm having a lot of trouble solving (and describing here). I've been trying to use VLOOKUP, though that may not be the function that I need to solve the problem.

I have a set of data in 'Tab 1' ranging from columns A through AT and from rows 1 through 3000.

The main data elements I'm dealing with are in Columns A and C. The pertinent data in Columns A and C look like this:

Column A ____Column C
V10000####1 V10000
V20000####1 V20000
V30000####1 V30000
V10000####2 V10000
V30000####2 V30000

There are 200 unique values in Column C and Column A starts with one of those values followed by additional Numbers and Letters.

The task I've been set is to create a separate tab for each unique value in Column C (which equates to 200 tabs) and set it up to list all instances where column A starts with that value.

For Example, 'Tab 2' will list all data in Column A that starts with "V10000", 'Tab 3' will list all data in Column A that starts with "V20000", etc.

I've tried to use VLOOKUP, but it is looking in Column A for the exact value but isn't finding it.

I tried to input the formula =VLOOKUP(V10000, A1:AT3000, 1, FALSE) hoping to get V10000####1
V10000####2

but I'm just getting #N/A because it's looking for V10000 exactly.

Does anyone have any suggestions on how to solve my problem? I've tried to explain as best I can, but if you need additional clarification, let me know and I'll try to answer as best I can.

Assuming the sheet for V10000 starts on row2 (row1 = header), try adapting this:
Where B1 holds "V10000" (no quotes, though)

A2 formula: =VLOOKUP($B$1&"*"&ROW()-1,Sheet1!$A$2:$A$6,1,FALSE)

or without the helper cell
=VLOOKUP("V10000"&"*"&ROW()-1,Sheet1!$A$2:$A$6,1,FALSE)

Here is my stock macro for parsing data from a "Data" sheet to sheets of their own. I've tweaked it for your requirements.

NOTE: the variables near the top you'll need to tend to:

vCol = 3 (we're getting the unique values from column 3)
ws = Sheets("Data") (edit this to match the name of your master data sheet)
vTitle = "A1:Z1" (this is the row of titles across the top of your data, edit if needed

Sub ParseItems()
'JBeaucaire  (11/11/2009)
'Based on selected column, data is filtered to individual sheets
'Creates sheets and sorts alphabetically in workbook
Dim LR As Long, i As Long, MyCount As Long, vCol As Long
Dim ws As Worksheet, MyArr As Variant, vTitles As String
Application.ScreenUpdating = False

'Column to evaluate from, column A = 1, B = 2, etc.
   vCol = 3
   
'Sheet with data in it
   Set ws = Sheets("Data")

'Range where titles are across top of data,
'as string, data MUST have titles in this row, edit to suit your titles locale
    vTitles = "A1:Z1"
    
'Spot bottom row of data
   LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row

'Get a temporary list of unique values from column A
    ws.Columns(vCol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("EE1"), Unique:=True

'Sort the temporary list
    ws.Columns("EE:EE").Sort Key1:=ws.Range("EE2"), Order1:=xlAscending, Header:=xlYes, _
       OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

'Put list into an array for looping (values cannot be the result of formulas, must be constants)
    MyArr = Application.WorksheetFunction.Transpose(ws.Range("EE2:EE" & Rows.Count).SpecialCells(xlCellTypeConstants))

'clear temporary worksheet list
    ws.Range("EE:EE").Clear

'Turn on the autofilter, one column only is all that is needed
    ws.Range(vTitles).AutoFilter

'Loop through list one value at a time
For i = 1 To UBound(MyArr)
    'We normally filter by the vCol, but this macro is coded to filter by column A instead
    ws.Range(vTitles).AutoFilter Field:=1, Criteria1:=MyArr(i) & "*"
    
    If Not Evaluate("=ISREF('" & MyArr(i) & "'!A1)") Then    'create sheet if needed
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MyArr(i)
    Else                                                     'clear sheet if it exists
        Sheets(MyArr(i)).Move After:=Sheets(Sheets.Count)
        Sheets(MyArr(i)).Cells.Clear
    End If
    
    ws.Range("A1:A" & LR).EntireRow.Copy Sheets(MyArr(i)).Range("A1")
    ws.Range(vTitles).AutoFilter Field:=1
    MyCount = MyCount + Sheets(MyArr(i)).Range("A" & Rows.Count).End(xlUp).Row - 1
    Sheets(MyArr(i)).Columns.AutoFit
Next i

'Cleanup
ws.AutoFilterMode = False
MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " & MyCount & vbLf & "Hope they match!!"
Application.ScreenUpdating = True
End Sub

This macro will create the sheets for you as well as order them sequentially as it parses the data to them.

That sounds like something you could use the macro recorder to get base code to add to my macro...