VLOOKUP produces error #N/A

[Emil Veit]

Dear community,
I'm at a loss understanding, why in the middle of a simple table I get
scattered #N/A errors,
where at other places of the column the function VLOOKUP(K2;S2:T12;2;FALSE)
works perfectly.

I know the data of the table array should be organized in ascending.
This is true for my first column of the table array, but unfortunately the
second column is in descending order.
Could that be the trouble. Can anything be done about it?
I'd appreciate your help.

Emil

 

[J.E. McGimpsey]

Since you use FALSE in the 4th argument, your column S values don't
need to be in ascending order. The order of your second column never
matters - only if the 4th argument is TRUE does the order of the
first column matter.

Check that your data is in the same format - i.e. if your column S
values are numbers, make sure that the value in K2 is a number, not
entered as Text. Likewise, make sure that neither your table nor
your lookup value have unwanted leading or trailing spaces or other
characters.

 

[Ken Wright]

Nope. The 4th argument of False will stop you needing it sorted, but if you are
getting #NA errors then it is not finding the data, meaning it isn't there. If
what you are seeing would lead you to believe that the dat really is there, then
this could be down to leading/trailing spaces in your data, or the way in which
you are constructing your query formula.

You may need to give us some example data and the formulas you are using.

Failing that then I'm still on holiday just kicking my heels somewhat and
chilling out, and so am quite happy for you to send me a workbook if you are
able and I'll take a look at it.

You would need to take the NOSPAM bit out of my email address, and be aware that
for my day job I work for Lockheed Martin, so don't send me anything I shouldn't
be seeing.