how - vlookup excel 2016

Excel: necesita ayuda con la función de búsqueda inusual(la columna ordenada es diferente de la columna de búsqueda)

Question

Tengo problemas para construir una fórmula de búsqueda inusual en Excel.

Normalmente, usaría VLOOKUP (), pero la trampa es que quiero buscar un número en una columna de una tabla que está ordenada en otra columna. Vlookup solo puede buscar elementos en la columna ordenada.

El escenario es el siguiente:

• La tabla se ordena de forma ascendente en la columna B.
• Para un parámetro P dado, ahora quiero encontrar el primer valor A, empezando desde arriba y abajo, que es mayor o igual que ese parámetro P.
• La función debería devolver el valor B correspondiente.

Tabla (parte desactivada, la tabla completa es mucho más grande):

 A B
1 640 4.5
2 1600 7.0
3 640 7.5
4 1280 12.0
5 1920 16.5
6 2560 21.0
7 1600 19.8
8 3200 26.8
9 4800 33.8

Por ejemplo , supongamos que mi parámetro es 1100, luego quiero que mi fórmula muestre 7.0, porque la primera entrada al buscar hacia abajo en la columna A que es mayor o igual que 1100 es 1600, que tiene un valor B correspondiente de 7,0

Intenté mi suerte con Array Formula''s (también conocida como "fórmula de ctrl-shift-enter") y construí algo como esto:

{=INDEX(table;
MATCH(MIN(IF(columnA-$C1>=0;columnA;FALSE));
IF(columnA-$C1>=0;columnA;FALSE);
0);
2)}

con C1 que contiene mi parámetro, tabla el rango A1: B9, y la columna A el rango B1: B9

Pero eso no funciona (en el ejemplo anterior, devuelve 12.0 porque 1280 es seleccionado por la función MIN ()).

Solución que no quiero usar: podría escribir algo de VBA para revisar la tabla, pero no quiero hacerlo (debido a las molestas ''advertencias de macro'', y porque Excel en Mac ya no es compatible con VBA )

¿Alguien alguna pista?

Answer 1

Puedes usar

{=INDEX(B1:B9,MIN(IF(A1:A9>C1,ROW(A1:A9),FALSE)))}

Tenga en cuenta que estoy usando la configuración de idioma inglés en el Reino Unido: tendrá que modificar la fórmula para que coincida con su configuración regional, reemplazar las comas con punto y coma, etc.

Answer 2

Si no desea una fórmula de matriz, puede intentar = DESPLAZAR ($ C $ 5, COINCIDIR ($ A $ 17, B5: B13), 0) donde C5 es el inicio de su columna B, B5: B13 es usted una columna y A17 es el valor para hacer coincidir.

Edición de Ninja: Aquí hay una versión que funciona para el bit menor o igual a su especificación. = OFFSET ($ C $ 5, IF (ISNA (MATCH ($ A $ 17, B5: B13,0)), IF (ISNA (MATCH ($ A $ 17, B5: B13)), 0, MATCH ($ A $ 17, B5 : B13)), MIN (MATCH ($ A $ 17, B5: B13,0) -1, MATCH ($ A $ 17, B5: B13))), 0)

Answer 3

Suponiendo que la tabla de datos está en el rango A2: B10, con el valor que está buscando en la celda B1, lo siguiente parece funcionar:

 =IF(B1<A2,B2,IF(ISNA(VLOOKUP(B1,$A$2:$B$10,2,FALSE)),INDEX(B2:B10,MATCH(B1,A2:A10)+1),VLOOKUP(B1,$A$2:$B$10,2,FALSE)))

Lo edité para dar cuenta cuando el valor de búsqueda es más pequeño que cualquiera de los valores en la lista.