☰

Excel - 如何使用 vlookup 返回多个值？

tag-icon • tag-icon microsoft-excel vlookup
Excel - 如何使用 vlookup 返回多个值？

我希望使用 Excel 查找并返回给定键的多个参考值。查找所做的事情和我需要的非常相似-但只返回一个匹配。

我猜想这会涉及数组返回和处理方法，尽管我之前没有处理过这些方法。一些谷歌搜索开始依赖 if([lookuparray]=[value],row[lookuparray]) 作为解决方案的一部分 - 尽管我无法让它返回单个匹配项...

例如，如果我有以下参考数据：

Adam    Red
Adam    Green
Adam    Blue
Bob     Red
Bob     Yellow
Bob     Green
Carl    Red

我正在尝试获取右侧的多个返回值。（如果可能，请用逗号分隔）

Red      Adam, Bob, Carl
Green    Adam, Bob
Blue     Adam
Yellow   Bob

（我已经有了左边的键值——不需要拉出这些值）

对于如何处理此上下文中的多个值，任何帮助都将不胜感激。谢谢。

答案1

假设您想要如上所述的公式方法（不使用 VLOOKUP，但仍使用公式），下面是我布置数据的方式：

数据布局

然后我在单元格 C12 中使用了以下公式：

=INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1, ""), 1))

这是一个数组公式，因此将其复制并粘贴到单元格后，您必须点击Ctrl+Shift+Enter。然后，我将其拖到右侧和底部。

如果没有剩余的值，则会出现错误#NUM!，我在上传的图像示例中给出了黄色的示例。

我认为，如果您有大量行，VBA/Macro 方法将是更好的解决方案。

答案2

以下是 VBA 解决方案。首先，结果如下所示：

截屏

代码如下：

Option Explicit
Function LookupCSVResults(lookupValue As Variant, lookupRange As Range, resultsRange As Range) As String

    Dim s As String 'Results placeholder
    Dim sTmp As String  'Cell value placeholder
    Dim r As Long   'Row
    Dim c As Long   'Column
    Const strDelimiter = "|||"  'Makes InStr more robust

    s = strDelimiter
    For r = 1 To lookupRange.Rows.Count
        For c = 1 To lookupRange.Columns.Count
            If lookupRange.Cells(r, c).Value = lookupValue Then
                'I know it's weird to use offset but it works even if the two ranges
                'are of different sizes and it's the same way that SUMIF works
                sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
                If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
                    s = s & sTmp & strDelimiter
                End If
            End If
        Next
    Next

    'Now make it look like CSV
    s = Replace(s, strDelimiter, ",")
    If Left(s, 1) = "," Then s = Mid(s, 2)
    If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)

    LookupCSVResults = s 'Return the function

End Function

答案3

  1. 交换列，使得颜色在 A 列，名称在 B 列，然后按颜色排序。

  2. C2 中的公式（将其复制到列下）：=IF(A2<>A1,B2,C1 & ", " & B2)

  3. D2 中的公式（将其复制到列下）：=A2<>A3

  4. 在 D 列中筛选“TRUE”以获得所需结果。如下所示：

在此处输入图片描述

答案4

如果您想要使用公式方法，那么在单独的单元格中获取结果要简单得多，因此我们假设您的第一个表格是 A2:B8，并且颜色再次列在 D2:D5 中。在 E2 中尝试此公式

=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$D2,ROW($B$2:$B$8)-ROW($B$2)+1),COLUMNS($E2:E2))),"")

确认CTRL+SHIFT+ENTER并横向和纵向复制。当火柴用完时，您将得到空白。

公式假定使用 Excel 2007 或更高版本 - 如果是早期版本，则可以使用 COUNTIF 而不是 IFERROR，即

=IF(COLUMNS($E2:E2)>COUNTIF($B$2:$B$8,$D2),"",INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$D2,ROW($B$2:$B$8)-ROW($B$2)+1),COLUMNS($E2:E2))))

相关内容