excel - Vlookup usando 2 columnas para hacer referencia a otra

excel-formula textjoin

Question

Estoy tratando de hacer una búsqueda bajo una circunstancia de nombre y apellido para obtener una edad. Esto se hará dentro de la Columna A, luego en la Columna B. Si se encuentra en la Columna A, Continúe a la Columna B, Si se encuentra en la Columna B, ponga la edad en J3 que proviene de la Columna C, si no, ponga "Ninguno".

Aquí hay un ejemplo:

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

J3 es lo que tengo hasta ahora. ¿Necesito anidar un Vlookup para verificar la Columna A, luego la Columna B para obtener la edad?

Aquí hay un ejemplo de la lista de tablas:

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

J3 = 55.

Answer 1

Muchas maneras:

1. Manejo de devoluciones de números:

Si sus valores de retorno son números y la coincidencia es única (solo hay un John Doe en los datos) o desea sumar los retornos si hay múltiplos, entonces el uso de SUMIFS es el método más rápido.

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

2. Con devoluciones no numéricas

Si los retornos no son numéricos o hay múltiplos, entonces hay dos métodos para obtener la primera coincidencia en la lista:

a. Una columna auxiliar:

En una cuarta columna ponga la siguiente fórmula:

=A1&B1

y copie la lista

Luego use INDEX / MATCH:

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

segundo. La fórmula de la matriz:

Si no desea o no puede crear la cuarta columna, use una fórmula de tipo matriz:

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

Las fórmulas de tipo matriz deben limitar el tamaño de los datos al conjunto de datos.

Si su conjunto de datos cambia de tamaño regularmente, podemos modificar lo anterior para que sea dinámico agregando más ÍNDICE / PARTIDO para devolver la última celda con datos:

=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))

Esto permitirá que el conjunto de datos crezca o se reduzca y la fórmula solo iterará entre aquellos que tienen datos y no la columna completa.

Los métodos descritos anteriormente se establecen en el orden de Mejor-Mejor-Bueno.

3. Para obtener múltiples respuestas en una celda

Si no desea sumar, o los valores de retorno son texto y hay varias instancias de John Doe y desea que todos los valores se devuelvan en una celda, entonces:

a. Si tiene Office 365 Excel, puede usar una forma de matriz de TEXTJOIN:

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

Al ser una fórmula de matriz, debe confirmarse con Ctrl-Shift-Enter en lugar de Enter al salir del modo de edición. Si se hace correctamente, Excel colocará {} alrededor de la fórmula.

Al igual que la fórmula AGREGADA anterior, debe limitarse al conjunto de datos. Los rangos se pueden dinamizar con las funciones INDEX / MATCH como las anteriores también.

segundo. Si uno no tiene Office 365 Excel, agregue este código a un módulo adjunto al libro:

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

Luego use la fórmula TEXTJOIN () como se describió anteriormente.