vba - "if contains text then vlookup"需要复杂的 Vlookup/VBA 函数或宏

Question

我有一个问题，http://goo.gl/i82eA这是我具有所需输出的示例数据。目前我有一个用户定义的函数，它手动使用许多 if 语句来完成这项工作，但如果它在列中找到某种颜色并返回与其对应的颜色图，我希望能够执行类似 vlookup 的操作。

或者像过滤器函数一样使用过滤器功能，例如过滤所有包含蓝色的单元格并为目标单元格提供蓝色，然后在颜色表中使用下一个值运行下​​一个过滤器。

Color   ColorMap     Text             Required Outputblue    blue         Deep Blue Shoe   Blue (if Text contains blue return blue)red     red          Deep red Shoe    red (if Text contains red return red) etctan     brown        Tan Shoe         brownnavy    blue         Navy Emp Shoe    bluejade    green        Jade Shoe        greenplum    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)

提前致谢

ps:这是我现在使用的代码

代码:

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

Answer 1

这会查找 Text值并返回 ColorMap值(value);如果找到多个匹配项，则返回“multicolored”。注意:这是一个数组公式 - 使用 Ctrl+Shift+Enter 输入。
=IF(SUM(IF(ISNUMBER(SEARCH(A$2:A$7,C2)),1,0))>1,"multicolored",LOOKUP(2^15,SEARCH(A$2:A$7,C2),B$2:B$7))
这是一个做同样事情的函数。它要求引用表是一个命名范围。像这样调用函数:=ColorMap(C2,"ColorTable")在哪里 ColorTable是一个命名范围，引用 $A$2:$B$7 .

Public Function ColorMap(LookupValue As Variant, LookupTableName As String) As String
    Dim vTable As Variant
    Dim lIdx As Long
    Dim sColor As String

    ' transfer lookup table to 2D range & loop through to find matches
    vTable = Names(LookupTableName).RefersToRange.Value
    For lIdx = LBound(vTable, 1) To UBound(vTable, 1)
        If UCase$(LookupValue) Like "*" & UCase$(vTable(lIdx, 1)) & "*" Then
            sColor = sColor & ", " & vTable(lIdx, 2)
        End If
    Next lIdx
    If Len(sColor) > 2 Then sColor = Mid$(sColor, 3)
    ' map multiple matches to "multicolored"
    If InStr(sColor, ",") > 0 Then sColor = "multicolored"
    ColorMap = sColor
End Function