我有一张 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