Can vlookup do this or should I be looking at another formula?

[len3121]

I have 30 categories that correspond to a number on sheet 1 listed by column.

Number, Category Name
A1 = 1 , B1 =Reading
A2 = 2, B2 =Math

etc for 30 rows.

On sheet 2 column A has the category name listed. If there is only one name then I don't have a problem using vlookup to return its corresponding number from sheet 1. However, the majority of cells have multiple category names listed and vlookup returns an N/A. Is there a way for vlookup to check all the names in the cell and return all the corresponding numbers without having to add all the possible combinations in sheet 1?

So cell A1 on sheet 2 which reads Reading, Math would return 1, 2.

 

[Aladin Akyurek]

I have 30 categories that correspond to a number on sheet 1 listed by column.

Number, Category Name
A1 = 1 , B1 =Reading
A2 = 2, B2 =Math

etc for 30 rows.

On sheet 2 column A has the category name listed. If there is only one name then I don't have a problem using vlookup to return its corresponding number from sheet 1. However, the majority of cells have multiple category names listed and vlookup returns an N/A. Is there a way for vlookup to check all the names in the cell and return all the corresponding numbers without having to add all the possible combinations in sheet 1?

So cell A1 on sheet 2 which reads Reading, Math would return 1, 2.

Add the following code as a module to your wotkbook:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then invoke...

B2, control+shift+enter, not just enter, and copy down:

=SUBSTITUTE(aconcat(IF(ISNUMBER(SEARCH(Sheet1!$B$2:$B$30,$A2)), 
      ", "&Sheet1!$A$2:$A$30,"")),", ","",1)

 

[doofusboy]

Here's another way to do it. However, for vlookup to work, you need to put Category Name in column A of Sheet1 and Number in column B. Highlight all cells in columns A and B that contain values and name that range 'LookUpTable' [without quote marks].

Next, insert a module to your workbook and paste the following code into it:

Public Function LookUpAll(str As String) As String
Dim i As Integer
Dim strFound As String
    For i = 0 To UBound(Split(str, ","))
        strFound = strFound & Application.WorksheetFunction.VLookup(Trim(Split(str, ",")(i)), ThisWorkbook.Names("LookUpTable").RefersToRange, 2, False) & ", "
    Next i
    LookUpAll = Left(strFound, (Len(strFound) - 2))
End Function

Finally, on Sheet2, in column B, enter the formula:

=LookUpAll(A1)

You can then copy that formula down column B as far as needed.

 

[len3121]

Thanks to both of you. One question, and sorry for this but I am a complete newbie when it comes to macros/modules, how do I insert a module into my workbook?

Edit: scratch that .. i figured it out.. Thanks again.. worked like a charm

 

[Aladin Akyurek]

Thanks to both of you. One question, and sorry for this but I am a complete newbie when it comes to macros/modules, how do I insert a module into my workbook?

Open the workbook of interest.

Hit Alt + F11 at the same time.

Then activate Insert|Module.

 

[doofusboy]

Right click the tab at the bottom of your screen that says 'Sheet1'.

Select 'View Code' from the menu presented and you will be placed in the IDE (integrated development environment).

From there, select 'Insert' from the menu and then select 'Module'.

You should see an icon called 'Module1' show up.

Double-click the module icon and paste in the code I provided.

 

[len3121]

Thanks guys, I appreciate the help