VLOOKUP pulls wrong cell

[donyc]

Hi all, I am having some trouble with VLOOKUP.. Would anyone have any thoughts where am I going wrong here? Would be much appreciated.. Thanks so much

Dan

What I am doing:
1) total 3000 rows
2) need to fill values in column B
3) identifier is in column Q
4) data table is in tab "static"
5) some of the values of in column B are blank, so I am only telling VLOOKUP to fill blank values

PROBLEM:
First 500 rows have values in column B, when I go on row 501(blank cell), vlookup pulls my identifer from Q1 as opposed to Q501

code I use:

Please Login or Register  to view this content.

[Leith Ross]

Hello Dan,

VLOOKUP will return the the first matching value it finds in the search column. Is the value of Q1 not a correct match? Are you matching numbers or strings?

[nimrod]

data table is in tab "static"

I'm confused ... you've indicated that your data Sheet is called "Static" but then in you formula you've indicted that the data sheet is called "data"


"=VLOOKUP(Q3 ,data!A:D,4,0)"

[donyc]

sorry all to clarify points below:


1) my tab where all the info is in called static(i have labeled it data before..srry for confusion)
2) all values are numeric
3) my column B has 3,000 rows, some of them are blank cells, so I use below VLOOKUP to fill those blank cells.. my identifier is in column Q.. all my static/data is in tab "static"
4) every day my column B will have some blank cells so I use vlookup to find those specific blank cells.. in this example, the first 500 rows are ok but B501 has missing value, so VLOOKUP then should pick it up by identifier in Q501..but it pulls it from Q1

basically, is excel smart enough to find the identifier to look up against the blank cell? in this case would be Q501

Please Login or Register  to view this content.

[donyc]

I put together a quick test file, should make it more clear what the issue is..
thanks again!
Dan

[nimrod]

Please Login or Register  to view this content.

[donyc]

thanks.. that worked on my test file but is breaking on my original data one..
I get "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class

I changed the coding to match my original columns.. any idea what this error means?

it breaks on line

Please Login or Register  to view this content.

"2" i assume for value I am looking up in columb B "static" tab right?

[nimrod]

VLookup(Bval, IDtable, 2, False)

Bval = BlankCell.Offset(0, 1).Value
This line is getting the Id number from one column to the right of the blank


Set IDtable = Sheets("static").Range("A:B")
IDtable is columns A and B on a sheet named "static"

2
This means that the return value will come from the second column of IDtable

False
Means vlookup is looking for exact match

[donyc]

perfect. that did the trick

thanks!

[nimrod]

What was the problem ?

[donyc]

I had to change the Offset column numbers....thanks again for the help