Excel vlookup 多个返回值

Excel vlookup 多个返回值

我有一张 Excel 表,其中一列是姓名列表,下一列是一些关联值。其中一些姓名在下一列中出现了几次,但值不同。

例如:

John  AA
James AE
Trent TE
Jim   CE
Trent AX
Jim   XC

我希望从 vlookup 函数返回的结果是:

John  AA
James AE
Trent TE AX
Jim   CE XC

但是 vlookup 只能返回一个值,还有其他函数可以帮助我解决这个问题吗?

答案1

我想建议以下解决方案,只需检查屏幕截图。

在此处输入图片描述

来源数据范围是A1:B7(不包括标题)

结果数据范围是A11:B14

首先创建一个唯一名称列表,写这个单元格 A11 中的公式

{=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF($A$11:A11,$A$2:$A$7),0)),"")}

对于最终结果,在 B11 中写入公式

{=IFERROR(INDEX($B$2:$B$7, SMALL(IF($A11=$A$2:$A$7, ROW($A$2:$A$7)-ROW($A$2)+1), COLUMN(A1))),"")}

将公式单元格写入并向下拖动,直到需要为止。

希望这对你有帮助,我已发布经过我测试的解决方案。

答案2

没关系,发现一些运行良好的代码。

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

相关内容