Vlookup issues

[Pablo269]

Hi

Just wondering if anyone cant help me out on this?

I posted earlier with an issue, i couldnt explaine it properly so i started to make a work around but i now have another issue caused by this

I am searching through a table of of 8000 lines and want to use a VLookup to search for an unique ID and something really strange is happening and i cant understand why as i have copied the unique code straight from the data it is searching

the data range that is being searched is A4:K8000 which has been named as a range of "TeamFigures" so the forumla that i am

Q5 is pulling the unique referance

"=VLOOKUP(Q5,TeamFigures,2,FALSE)"

some times this forumla is working other times it is returning a N/A

Any idea's, also can this be written in VBA instead of code on the sheet?

Cheers

P

[jeffreybrown]

If it doesn't work on the worksheet then why would it work any better in VBA?

Can you post a sample workbook and maybe we can spot a reason why!

[Pablo269]

Unfortunatly i cant upload a copy of it as it has corp sensitive data in it, but does a vlookup have a limit that it can lookup

the formula looks ok and i have used the same thing but not on the same scale before

the reason why i asked about doing it VBA becuase then it is hiden from the end user

Cheers

P

[jeffreybrown]

The only limit I know of, a Vlookup can only search from left and return a value to the right.

Without a sample workbook or any further guidance it's hard to recommend something, but take a look at this sample workbook which used a Vlookup in VBA.

[Pablo269]

Found a work around for some reason the unique string in the figures book which is sent from head office has two row's

eg

000000001
000000001 Total

So i have done this it does the trick but too sloppy for my liking lol, the data in "Data!D8" is the 000000001 unique id


N1 = "="0"&Data!D8&" Total"
"=VLOOKUP(N1,A1:K7800,3,FALSE)"

at least it works lol, looking at what i have had to do how would i put theat in to the VBA you uploaded

Thanks so much for this

[jeffreybrown]

Okay post back if you have any further questions.