SOLVED

Función Buscarx

%3CLINGO-SUB%20id%3D%22lingo-sub-2611729%22%20slang%3D%22es-ES%22%3EFindx%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2611729%22%20slang%3D%22es-ES%22%3E%3CP%3EDear%2C%20I%20have%20a%20problem%20using%20the%20xlookup%20function%20on%20double-entry%20tables.%20I'm%20using%20this%20nested%20function%20to%20look%20for%20a%20value%20first%20in%20a%20row%20of%20the%20table%20and%20then%20in%20the%20columns.%20The%20problem%20is%20that%20if%20I%20use%20a%20fixed%20initial%20value%20(e.g.%201.7)%20for%20searching%20the%20rows%20and%20then%20another%20unique%20value%20(e.g.%202.6)%20in%20the%20columns%2C%20it%20gives%20me%20a%20correct%20result%2C%20but%20if%20instead%20of%20a%20fixed%20value%20I%20use%20a%20formula%20of%20the%20form%20(x%2By)%2Fz%20(e.g.%20(3.2%20%2B%200.2)%2F2)%2C%20which%20is%20the%20same%20value%201.7)%2C%20it%20gives%20me%20the%20next%20value%20in%20the%20table.%20I've%20tried%20different%20tables%20and%20it's%20always%20the%20same.%20Also%2C%20in%20the%20latter%20case%20if%20I%20select%20the%20match%20mode%20as%20exact%20(option%200)%2C%20it%20doesn't%20give%20me%20a%20value%3B%20it%20only%20allows%20me%20the%20approximate%20value%20option%20(1)%2C%20which%20is%20where%20it%20delivers%20the%20result%20corresponding%20to%20the%20next%20value%20in%20the%20table.%3C%2FP%3E%3CP%3EPlease%20could%20you%20help%20me%20to%20find%20out%20what%20I%20may%20be%20doing%20wrongly%20or%20if%20someone%20has%20had%20the%20same%20thing%20happening%20and%20if%20they%20have%20been%20able%20to%20solve%20it.%20I%20need%20to%20use%20an%20some%20cases%20this%20function%20using%20formulas%20as%20the%20initial%20search%20value.%20Thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2611729%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2612028%22%20slang%3D%22en-US%22%3ERe%3A%20Funci%C3%B3n%20Buscarx%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2612028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1120573%22%20target%3D%22_blank%22%3E%40charnay%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMost%20probably%20that%20is%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2Ffloating-point-arithmetic-inaccurate-result%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EFloating-point%20arithmetic%20may%20give%20inaccurate%20result%20in%20Excel%20-%20Office%20%7C%20Microsoft%20Docs%3C%2FA%3E%26nbsp%3B%2C%20try%20to%20ROUND()%20result%20of%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Estimados, tengo un problema para usar la función buscarx en tablas de doble entrada. Estoy usando esta función anidada para buscar un valor primero en una fila de la tabla y después en las columnas. El problema es que si uso un valor inicial fijo (ej. 1,7) para la búsqueda en las filas y después otro valor único (ej. 2,6) en las columnas, me entrega un resultado correcto, pero si en lugar de un valor fijo utilizo una fórmula de la forma (x+y)/z (ej. (3,2 + 0,2)/2), que es el mismo valor 1,7), me entrega el valor siguiente de la tabla. He probado con distintas tablas y siempre pasa lo mismo. Además, en este último caso si selecciono el modo de coincidencia como exacta (opción 0), no me entrega un valor; sólo me permite la opción de valor aproximado (1), que es donde entrega el resultado correspondiente al valor siguiente de la tabla.

Me podrían ayudar por favor para saber qué puedo estar haciendo de forma errónea o si a alguién le ha pasado lo mismo y si lo ha podido solucionar. Necesito usar an algunos casos esta función utilizando fórmulas como valor inicial de búsqueda. Gracias.

 

 

 

2 Replies

@charnay 

Most probably that is Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs , try to ROUND() result of the formula.

best response confirmed by charnay (New Contributor)
Solution

Hola @Sergei Baklan. Realicé lo que me sugeriste y funcionó. Además, probé el método 2 que aparece en la página web Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs y también funcionó.

Muchas gracias por tu respuesta. Me ayudó mucho.

Saludos.