VLookup value in a cell containing multiple entries

**ayresm** · 08-12-2010, 05:54 AM

Hi,

I have attached a basic spreadsheet to demonstrate the problem that I want to resolve.

I have two workbooks...

Workbook 1 (represented by 'Table 1'):

This includes 'Scenario ID' and an empty column that I want to automatically fill with multiple 'Script ID' values from multiple cells in the second work book.

Workbook 2 (represented by 'Table 2'):
This contains Scripts, and the 'Scenarios' that map to these scripts.

I want to use some sort of VLOOKUP or SEARCH function, or see if there is some VB that can be written to lookup the 'Scenario ID' value in workbook 1, in the 'Additional Scenarios Covered' column in workbook 2.

I'm currently presented with two problems:

1. A VLOOKUP function cannot search an array for cells that contain the value - they will only return details when the cell exactly matches.
2. A VLOOKUP function will only return the first value when found, whereas I need a formula that can return each and every instance throughout my table.

So, at the moment I am doing this manually, but both workbooks contain over 1000 entries so this really isn't ideal and is time consuming!

If anyone can help I'd be extremely appreciative!

**sandeep.warrier** · 08-12-2010, 06:17 AM

Hi

The code given below does what you want. You may have to make some minor changes for your actual setup.

For example: Suppose Table 1 exists in workbook 1 (named "WB1" for example) Sheet 1 then you will need to change the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

OR

Define a variable

Please Login or Register  to view this content.

Then use

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**WinteE** · 08-12-2010, 08:18 AM

Take a look here www.excelguide.eu, Excel, Lookup, Lookup several values.

**ayresm** · 08-12-2010, 10:16 AM

Originally Posted by sandeep.warrier

Hi

The code given below does what you want. You may have to make some minor changes for your actual setup.

For example: Suppose Table 1 exists in workbook 1 (named "WB1" for example) Sheet 1 then you will need to change the line

Hello,

A massive thank you to you both for this.

I've played around with the code provided by sandeep.warrier, but unfortunately my knowledge of VB is very limited and I've not been able to correctly make the adjustments for my setup. I've attached two separate documents now, and wondered if anyone would be able to tweak the code provided above for this setup?

So here:
- Scenarios Document Layout represents Workbook 1
- Script Document Layout represents Workbook 2
- I want to map column A from Script Document into column T from the scenario document, whenever column B from the Scenario document appears in column AK in the Script document.

This is the same as the originial example, but now covered across two documents.

Again, a huge thanks, as this does exactly what I need it to do!

Matt