Vlookup behaving eratically and producing different results

[markDuffy]

Hi Guys
I am utterly perplexed and frustrated.
I am using vlookup in a file which I have used many times before.
There are a batch that it will not find.
I am uploading a file. Also attached will be VBA which covers lots of other sheets in this file which I have deleted from this copy to keep it light. Please feel free to have a look if it helps. I am uploading to the formulas and functions section.
I have a control sheet where new descriptions are added and the company entered. I have done this.
But the link

Please Login or Register  to view this content.

will not enter some but does for most. I have checked for duplicates in the table DescriptionToCompany.
I have copied from one entry to the other to be sure its a precise match.

Also when I sort under Description in the control sheet I get a different set of matches in BDC2 sheet than if I sort under Company in the control sheet . Sequence is there just so I can revert to a given position and identify the newly added entries. I understand that the table should be sorted by the left most column - IE description. Is that correct?

I know that you guys will have the answer to this. It will be something really simple and straight forward. I am a little thin on top but this will take the last bit of hair I have.
Looking forward for your comment and thanks in advance.

Mark

[Jacc]

The named range DescriptionToCompany does not go all the way down.

[Jacc]

Put this in cell N2 and copy down:

Please Login or Register  to view this content.

It uses the table names instead of the named range, it's a more robust solution.

[markDuffy]

Hi Jacc
AHHHH All morning before I dared ask. Thanks

What I have to work out now is how to make it dynamic so that it expands as I enter or copy more lines underneath.

Have a great day in Norway
Mark

[Jacc]

The formula I gave you already is 100% dynamic, the Excel Table expands automatically when you add new values. I would just delete any named range you can do without and use the Excel Table references instead.