VLOOKUP with multiple matches

**SUPPO_USN** · 03-15-2010, 09:45 PM

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.

**Palmetto** · 03-15-2010, 10:27 PM

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)

**JBeaucaire** · 03-16-2010, 04:23 AM

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

Please Login or Register  to view this content.

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

**SUPPO_USN** · 03-16-2010, 02:18 PM

JBeaucaire,

That coding was amazing. It did exactly what I spent hours trying to do manually in a minute. It is greatly appreciated.

r/

SUPPO_USN

**cesar1128** · 06-04-2012, 11:55 PM

JBeaucaire,
Your code works great.... I do have a question!..my workbook has 3 additional worksheets and i would like to move or copy those remaining worksheets once the file has been split into individual workbooks.