V-Lookup not working properly

[Guest]

I created a V-Lookup that should find the description of an article. The
strange thing is, that at first it wouldn´t find the correct description
although the article numer does exist. The correct article number is in row
467 and the other description it showed me was in row 780. I changed my
formula and added false at the end and for the one article I did indead get
the correct description, still don´t understand it because as long as the
article number exist it should have shown me the correct title.

But now comes the really stupid thing. I then copied the formula down
(autofill) and got for ever article number the same description. In my
desperation I sorted the matrix by the article number and suddenly I have the
correct result. Surely, I can´t be expected to always sort my matrix in order
to get a correct result. Where is here the logic for using V-Lookup?!

Although I have the right result now I do need to know why this happens as
an explanation when I am asked.

Does anyone know this problem?

Regards

Nicole

 

[Bob Phillips]

The False argument is there specifically so that you can do an exact match,
rather than an approximate match. That is why when the data was not sorted,
it is necessary to use False otherwise it will find the next largest value
that is less than the lookup value.

When you autofilled, did you make sure that the lookup table was anchored so
that it did not move, e.g.

=VLOOKUP(E1,$M$1:$P$200,2,False)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

I cannot replicate the problem, I get the correct results without sorting.

Can you give an example of the data and the formula?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)