SOLVED

Excel 2016 LOOKUP & TEXT cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2337546%22%20slang%3D%22en-US%22%3EExcel%202016%20LOOKUP%20%26amp%3B%20TEXT%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2337546%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20someone%20help%20me%20to%20undersatand%20why%20the%20lookup%20formula%20is%20not%20working%20for%20this%20text%20searchs%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20Juan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2337546%22%20slang%3D%22en-US%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-2337574%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20LOOKUP%20%26amp%3B%20TEXT%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2337574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F145009%22%20target%3D%22_blank%22%3E%40juan%20jimenez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVLOOKUP%20works%20from%20left%20to%20right.%20It%20finds%20nothing%20in%20first%20column%20of%20lookup%20range%20(column%20D)%20and%20returns%20an%20error.%3C%2FP%3E%0A%3CP%3EAlternatively%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(INDEX(%24D%243%3A%24D%241646%2C%20MATCH(C3%2C%24H%243%3A%24H%241646%2C0))%2C%20%22no%20such%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2337577%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20LOOKUP%20%26amp%3B%20TEXT%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2337577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F145009%22%20target%3D%22_blank%22%3E%40juan%20jimenez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bsays%2C%20the%20lookup%20table%20is%20not%20conveniently%20arranged%20for%20VLOOKUP.%26nbsp%3B%20INDEX%2FMATCH%20is%20more%20general%20but%2C%20if%20you%20wish%20to%20use%20VLOOKUP%2C%20it%20is%20possible%20to%20play%20tricks%20to%20build%20an%20array%20that%20works%20with%20the%20function.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20VLOOKUP(Appel%2C%0A%20%20%20%20%20IF(%7B1%2C0%7D%2CApellidos%2CNombre)%2C%20%0A%20%20%20%20%202%2C%20%0A%20%20%20%20%20FALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThe%20second%20line%2Fparameter%20builds%20a%202-column%20array%20in%20the%20correct%20order.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

hi,

 

can someone help me to undersatand why the lookup formula is not working for this text searchs?

 

Thank you, Juan

5 Replies
best response confirmed by juan jimenez (Frequent Contributor)
Solution

@juan jimenez 

VLOOKUP works from left to right. It finds nothing in first column of lookup range (column D) and returns an error.

Alternatively

=IFNA(INDEX($D$3:$D$1646, MATCH(C3,$H$3:$H$1646,0)), "no such")

@juan jimenez 

As @Sergei Baklan says, the lookup table is not conveniently arranged for VLOOKUP.  INDEX/MATCH is more general but, if you wish to use VLOOKUP, it is possible to play tricks to build an array that works with the function.

= VLOOKUP(Appel,
     IF({1,0},Apellidos,Nombre), 
     2, 
     FALSE)

The second line/parameter builds a 2-column array in the correct order.

I need someone to help me with the error message in Anova test

@Peter Bartholomew 

Nice trick, afraid with decreasing of performance, but that could be not critical on small and medium models.

@lonuegbustudentumgc 

I'd suggest you to start new conversation / thread here and specify more concretely which kind of errors do you have and on which model.