Vlookup 使用 2 列引用另一列 [英] Vlookup using 2 columns to reference another

问题描述

我试图在先姓后名的情况下进行 vlookup 以获取年龄.这将在 A 列内完成，然后在 B 列内完成.如果在 A 列中找到，继续到 B 列，如果在 B 列中找到，则将来自 C 列的年龄放入 J3 中，否则输入无".

I am trying to do a vlookup under a circumstance of first then last name to get an age. This will be done within Column A, then Column B. If found in Column A, Continue to Column B, If found in Column B, put age in J3 that comes from Column C else put "None".

这是一个例子:

J1 = John
J2 = Doe
J3 = =VLOOKUP J1 & J2,A1:C50,3,FALSE)

J3 是我目前所拥有的.我是否需要嵌套 Vlookup 来检查 A 列，然后是 B 列才能获得年龄?

J3 is what I have so far. Do I need to nest a Vlookup to check Column A, then Column B in order to get the age?

以下是表列表的示例:

A     B    C
Jeff  Vel  80
John  Fly  25
Jake  Foo  20
John  Doe  55

J3 = 55.

推荐答案

多种方式:

如果有新的动态数组公式:

If one has the new Dynamic array formulas:

=FILTER(C:C,(A:A=J1)*(B:B=J2))

如果不是，则:

1. 处理 Number 返回:

如果您的返回值是数字并且匹配项是唯一的(数据中只有一个 John Doe)或者您想对多个返回值求和，那么使用 SUMIFS 是最快的方法.

If your return values are numbers and the match is unique(there is only one John Doe in the data) or you want to sum the returns if there are multiples, then Using SUMIFS is the quickest method.

=SUMIFS(C:C,A:A,J1,B:B,J2)

1. 非数字返回

如果返回的不是数字或有多个，则有两种方法可以获取列表中的第一个匹配项:

If the returns are not numeric or there are multiples then there are two methods to get the first match in the list:

一个.辅助栏:

在第四列中输入以下公式:

In a forth column put the following formula:

=A1&B1

并复制列表

然后使用索引/匹配:

=INDEX(C:C,MATCH(J1&J2,D:D,0))

B.数组公式:

如果您不想或无法创建第四列，请使用数组类型公式:

If you do not want or cannot create the forth column then use an array type formula:

=INDEX(C:C,AGGREGATE(15,6,ROW($A$1:$A$4)/(($A$1:$A$4=J1)*($B$1:$B$4=J2)),1))

数组类型的公式需要将数据的大小限制为数据集.

Array type formulas need to limit the size of the data to the data set.

如果您的数据集大小定期更改，我们可以通过添加更多 INDEX/MATCH 来将上述内容修改为动态，以返回包含数据的最后一个单元格:

If your data set changes sizes regularly we can modify the above to be dynamic by adding more INDEX/MATCH to return the last cell with data:

=INDEX(C:C,AGGREGATE(15,6,ROW($A$1:INDEX($A:$A,MATCH("ZZZ",A:A)))/(($A$1:INDEX($A:$A,MATCH("ZZZ",A:A))=J1)*($B$1:INDEX($B:$B,MATCH("ZZZ",A:A))=J2)),1))

这将允许数据集增长或缩小，并且公式将只迭代那些有数据的数据而不是完整的列.

This will allow the data set to grow or shrink and the formula will only iterate through those that have data and not the full column.

上述方法按照最佳-更好-良好的顺序设置.

The methods described above are set in the order of Best-Better-Good.

1. 在一个单元格中获得多个答案

如果您不想求和，或者返回值是文本并且有多个 John Doe 实例并且您希望在一个单元格中返回所有值，那么:

If you do not want to sum, or the return values are text and there are multiple instances of John Doe and you want all the values returned in one cell then:

一个.如果您有 Office 365 Excel，则可以使用 TEXTJOIN 的数组形式:

a. If you have Office 365 Excel you can use an array form of TEXTJOIN:

=TEXTJOIN(",",TRUE,IF(($A$1:$A$4=J1)*($B$1:$B$4=J2),$C$1:$C$4,""))

作为数组公式，退出编辑模式时需要使用 Ctrl-Shift-Enter 而不是 Enter 确认.如果操作正确，Excel 会将 {} 放在公式周围.

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

像上面的 AGGREGATE 公式一样，它需要限制在数据集上.也可以使用上面的 INDEX/MATCH 函数使范围动态化.

Like the AGGREGATE formula above it needs to be limited to the data set. The ranges can be made dynamic with the INDEX/MATCH functions like above also.

B.如果没有 Office 365 Excel，则将此代码添加到附加到工作簿的模块中:

b. If one does not have Office 365 Excel then add this code to a module attached to the workbook:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

然后使用上述的 TEXTJOIN() 公式.

Then use the TEXTJOIN() formula as described above.

这篇关于Vlookup 使用 2 列引用另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！