☰

Búsqueda basada en dos criterios, uno un número y otro un rango

tag-icon • tag-icon microsoft-excel
Búsqueda basada en dos criterios, uno un número y otro un rango

Tengo dos listas, una con ID y porcentajes basados ​​en varios rangos y la otra con ID y totales. Necesito buscar el porcentaje en la primera hoja de cálculo según el ID y el total en la segunda hoja de cálculo.

ID  Bottom Top  Percentage
1   1      50       0.3
1   51     75       0.4
1   76              0.5
2   1               0.75
3   1      25       1
3   26     100      2
3   101    1000     3
3   1001   2000     4
3   2001            5

Second List
ID  Total Amount
1   100
2   146
3   1256

Respuesta1

Suponiendo que su primera tabla está en la Hoja1 y su segunda tabla está en la Hoja2 y que desea completar la columna Monto de su segunda tabla, la siguiente fórmula logrará esto para la primera celda Monto (celda C2) de la Hoja2. Esto requiere que la primera tabla se ordene por la columna ID y luego por la columna Inferior. Esta fórmula se puede copiar y pegar hasta el final de la tabla 2 después de haberla ingresado.

=VLOOKUP(B2, OFFSET(Sheet1!B:D, MATCH(A2, Sheet1!A:A, 0) - 1, 0, COUNTIF(Sheet1!A:A, A2)), 3)

Tenga en cuenta que la columna Superior en realidad no es necesaria para que esto funcione.

Desglosando esta fórmula, utiliza elVLOOKUPfunción para buscar en la columna Inferior el valor más cercano inferior o igual al valor Total y luego devuelve el valor en la columna Porcentaje para la misma fila. También tiene que limitar la búsqueda a filas que coincidan con el ID correcto, y lo hace con elOFFSETfunción. ElOFFSETLa función utiliza la fila del primer ID coincidente encontrado con elMATCHfunción y el recuento total de IDS coincidentes encontrados con elCOUNTIFfunción.

información relacionada