Excel 2016 LOOKUP & TEXT cells

Frequent Contributor



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)

@juan jimenez 

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


=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,

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.


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.