Vlookup behaving strangely

Hi

I'm not sure if there is something I'm missing or if I don't know how to use VLOOKUP but it seems to be behaving really strangely. I have a database at work that is supposed to use supplier numbers to bring back a supplier name but it just gives #NA result. I've checked and re-checked the formula to no avail. I've tried formatting the data, copying and pasting to ensure that they are the same but nothing works. I created a dummy worksheets with two columns and 4 rows as per the attached. If I change the value (B2 on sheet 2) I'm looking up to the fruit, it will bring up the #NA value. If I change the value to look for to the shop then it brings it up, even if I change the reference column from 1 to 2 it will always bring a result for the shop and #NA for the fruit name. I'm totally confused, perhaps I'm missing something. I'm using excel 2013.

sought column must be the 1st left

Hi

Thanks for the info, it makes more sense now. I have another issue, if the list is pulled from a database and we want to link a company to a guy based on supplier number (see attached), how would I do it? It seems to run through all the numbers and then stop. Should the numbers under column 'number' also be in ascending order? Lastly, if there are hidden rows, does this affect VLOOKUP?

Solved guys, thanks for all the help. I used the index and match formula to sort out the issue. Thanks again!