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.