Complex Vlookup/ VBA function or Macro Needed For "if contains text then vlookup"

Hi

I have a problem, http://goo.gl/i82eA this is sample data that I have with the required output. Currently I have a user defined function that is manually using many if statements to do the job but I want to be able to do something like a vlookup if it finds a certain color in a colum and return the colormap coresponding to it.

Or use like a filter function like filter all cells that contain Blue and give the destination cell with blue, and then run next filter with next value in an table of colors.

Color ColorMap Text Required Output
blue blue Deep Blue Shoe Blue (if Text contains blue return blue)
red red Deep red Shoe red (if Text contains red return red) etc
tan brown Tan Shoe brown
navy blue Navy Emp Shoe blue
jade green Jade Shoe green
plum red Plum Red Shoe multicoloured (if Text contains more than 1 color return multicolored)

So input should be like 2 columns for the data to be looked up 1 column for searching and the 1 column is the destination column if its a function

function_name(lookup_text,lookup_table,destination)


Thanks in advance








ps: here is the code Im using now

Function Colormap(strVal As String) As String

If (InStr(strVal, "red") > 0) Then
    Colormap = "Red"
End If

If (InStr(strVal, "Beige") > 0) Then
    Colormap = "Beige"
End If

etc..

End Function

post a workbook not a link

alfykunable,

Please see the attached workbook. The example assumes that you have entered all of the lookup values (the color to search for) and output text (corresponding colormap) from your current code into a table somewhere in your workbook. When you use the function to specify a search string, like "Deep Blue Shoe", it will look in this table for your lookup values and output text. In this example, I placed this table on a worksheet called setup. I then created a dynamic named range to refer to this table, using the following formula:

=OFFSET(setup!$A$2,0,0,COUNTA(setup!$A:$A)-1,2)

The OFFSET formula I used to refer the search range uses the COUNTA formula in the [height] parameter to return a table height that is equal to the number of non-blank cells in the specified range (in this case, column A). By using this method, I can add lookup values and colormaps by simply adding a row to the bottom of my table. COUNTA will recognize that another non-blank cell as been added to Column A and tell the OFFSET formula to "grow" one row bigger.

Please note that my table contains headers, which I don't want to be included in the search range when the function fires. That is why the OFFSET formula begins at cell $A$2...and which is also why there is a "-1" at the end of my COUNTA formula. If you do not want to include headers in you table, the named range formula should look like this:

=OFFSET(setup!$A$1,0,0,COUNTA(setup!$A:$A),2)

Here's a very quick explanation of how the function works: when you enter the COLORMAP function into a cell and identify the search string, the function will loop through the list of lookup values from the table you set up, looking for a match within your search string. If it finds one, it will return the colormap that corresponds with that lookup value. If it finds more than one match, it will return Multicolor.

Place the following code into any standard code module in the attached workbook, as the example workbook was uploaded without it.

Public Function COLORMAP(strVal As String) As String
    ' the application.volatile method below causes the worksheetfunction to calculate whevever
    ' calculation occurs in any cell on the worksheet. not essential; delete if not desired.
    Application.Volatile
    Dim rngClr As Range
    Dim lRow As Long, lMatchCount As Long
    
    With ThisWorkbook
        Set rngClr = Sheet2.Range("rng_lookup_value")   ' range containing lookup values and output text
        lMatchCount = 0  ' the number of lookup values returned in the search string
        With rngClr
            For lRow = 1 To .Rows.Count
                ' look in search string for lookup values
                If InStr(UCase(strVal), UCase(.Cells(lRow, 1))) > 0 Then
                    lMatchCount = lMatchCount + 1   ' count the number of lookup value matches
                    Select Case lMatchCount
                        Case Is = 1     ' if one lookup value is found, return output text
                            COLORMAP = .Cells(lRow, 2)
                        Case Is > 1     ' if more than one lookup value is found, return multicolor
                            COLORMAP = "Multicolor"
                    End Select
                End If
            Next lRow
        End With
    End With
End Function

I briefly tested this, and everything seems to be in order. Hopefully it works for you as well. If not, let me know.

Hi Ryan,

Thanks for the file,

works like a charm


just 1 pointer, how can i change it to be case insensitive?

Because In the Lookuptable I put
Plum Red - Red

but when i colormap(Plum Red) it gave Multicolored and not red why?

alfykunable,

It's not the case. If you add Plum Red - Red to your lookup table, it should work.