VLOOKUP values only if there is blanks cell

[donyc]

Hi all, I am working with vlookup on below scenario and curious if there is a better way to do this..I run a macro which populates

3 columns
5,000 rows (4,000 rows contain the data I need, but 1000 is missing identifier in column A..also my total number of rows will change daily)

I need to do a VLOOKUP (or perhaps there is another option? Lookup?) to find values for Blank cells in Column A based per static data table I have in Sheet3..if nothing is found to return comment "missing variable"

I was thinking of autofiltering for blanks and doing vlookup then? Is this the most efficient way to approach this?

Any suggestions much appreciated

Thanks so much
Dan

[JBeaucaire]

Stated differently...

1) You have a VLOOKUP() formula that searches column A and returns values from adjacent columns.
2) Sometimes the VLOOKUP does not find the value in column A due to missing values in that column
3) Would like the VLOOKUP to not return an error when that happens, return "missing variable" instead


Is this correct?

If so, you'll need to run the formula twice to check for errors. If your current VLOOKUP is:

=VLOOKUP(A1, Sheet1!A:C, 3, 0)

...you would have to try something like:

=IF(ISERROR(VLOOKUP(A1, Sheet1!A:C, 3, 0)), "missing variable", VLOOKUP(A1, Sheet1!A:C, 3, 0))

[donyc]

Hi JBeaucaire, all your assumptions below are correct and the formula works fine.. there is another part that is missing though:

Let's say Column A has 5,000 rows: I already have some filled in identifiers(let's say random 4,000 rows) and I basically just need to scan for any blanks and then do VLOOKUP on those blank cell.. this way I avoid doing VLOOK up for all 5,000 rows and just on the blank ones...and then ofcourse if VLOOKUP does not find the variable from the static table to return "missing variable"

Hope this make sense

Dan

[donyc]

I tried to use =if(cell="","",use my vlook up formula)

but, I can't seem to figure out how to implement it so it starts at A3( there has to be a starting point for this to work I assume?) and scans all the way down to last row which I define:
LastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

[JBeaucaire]

You show formulas and VBA code. What is happening here?

=IF(A1="", "", VLOOKUP(A1, Sheet1!$A$3:$C$1000, 3, 0))

[Palmetto]

Cross Posted at Ozgrid

[donyc]

so I got this one to work:

Please Login or Register  to view this content.

but I am trying to edit formula so it now comments if nothing was found but getting error in formula and it highlights my comment as a break: ""not in static instrument table

Please Login or Register  to view this content.

Any suggestions why this might be getting stuck?

thanks
Dan

[tigeravatar]

When you want the result to be a comment, it only needs one quotation mark around it, not two:

Please Login or Register  to view this content.

[donyc]

tried single quotes as well..didn't work..clueless what might be the problem here
any suggestions would be appreciated

[donyc]

to clarify when I use the below.. where there is nothing to look up I get value returned of 0 instead of "not in static instrument"

Please Login or Register  to view this content.