CODEWITHSUNDEEP
excelexcel-formulavba
D Hughes
D Hughes

Reputation: 21

VLookup - Visual Basic - how to set a range

I have a Vlookup formula set in cell D5 that checks some data in cell E5, does the Vlookup below, and returns the result:

=VLOOKUP(E5,StockCodesBBG!A:J,2,FALSE)

What I would like to do is to have a way of automatically counting the number of rows I have populated from E5 down, and then running the Vlookup formula for each of those corresponding cells.

For example, say I have data in E5,E6,E7, I would like the formula to apply itself into D5,D6,D7.

I can just Autofill this down by copying the formula, but it leaves a load of messy #N/A values where there is nothing in the E column to reference. How do I avoid the in cell error messages?

Upvotes: 2

Views: 709

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

You can AutoFill it but at the same time handle the #N/A so that you do not see those obnoxious values :)

If using xl2003, use this

=if(ISERROR(VLOOKUP(E5,StockCodesBBG!A:J,2,FALSE)),"",VLOOKUP(E5,StockCodesBBG!A:J,2,FALSE))

or

=if(ISNA(VLOOKUP(E5,StockCodesBBG!A:J,2,FALSE)),"",VLOOKUP(E5,StockCodesBBG!A:J,2,FALSE))

If using xl2007+, use this

=IFERROR(VLOOKUP(E5,StockCodesBBG!A:J,2,FALSE),"")

Check the Excel help on ISERROR,ISNA,IFERROR

Upvotes: 3

Related Questions