If cell contains "Specific Text" return value of cell beside it. Similar to VLOOKUP

Question

I have 2 sheets, one with a list of parts(sku) and all the vehicles that the part will fit on (fitment) separated by "^^" it looks like this:

sku | fitment


 1 |car1 ^^ car2 ^^ car3
 2 |car2 ^^ car3
 3 |car1
 4 |car1 ^^ car 2
 5 |car3

On my second sheet I have a list of cars that looks like this:

fitment

car1
car2
car3

For every car in sheet two, I would like to search the fitment in sheet one for that specific text and return the sku if it is found (separated by a delimiter). So it would look like this:

fitment |skus
-------------
car1|1,3,4
car2|1,2,4
car3|1,2,5

Answer 1

Try this small User Defined Function:

Public Function CarFinder(clist As Range, carID As String)
   Dim cell As Range
   CarFinder = ""

   For Each cell In clist
      If InStr(1, cell, carID) > 0 Then
         If CarFinder = "" Then
            CarFinder = cell.Offset(0, -1).Value
         Else
            CarFinder = CarFinder & "," & cell.Offset(0, -1).Value
         End If
      End If
   Next cell
End Function

An example of its use:

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=CarFinder($B$1:$B$100,Z9)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Answer 2

Gary's Student's UDF more closely matches what you're trying to do, but here's a version that works without VBA. The catch is you need separate columns for each SKU. Organize your output like this, with cars on the left, and SKUs across the top:

        1    2    3    4    5
car1
car2
car3

Then, assuming that car1 is in cell D2, and 1 is therefore in cell E1, use this formula in cell E2:

=NOT(ISERROR(SEARCH($D2,INDEX([Fitments],MATCH(E$1,[SKUs],0)))))

You can then autofill the formula down and across to fill out the rest of your data. Returns either TRUE or FALSE.