Perform a vlookup only when there is a value in the Loopup value?

[marcvanderpeet12]

Dear all,


I'm working with a sheet now in which I have to use a vlookup functions for a whole row. because the rowsize of the table change everytime I just created a macro which performs a vlookup from row 1-100.


But now I get N/A for all value which are not filled in. Does anybody know a way in which I can perform a vlookup only when where is a value in the lookup value.


Thanks in advance.


Dear Regards,


Marc

 

[kaushik03]

Hi marcvanderpeet12,


Actually in this case you can avoid using macro.You can make the column / rows dynamic by using a combination of offset-counta formula in 'refer to' box of 'namemanager'


I assume Your data is placed in a range of A2:D9 at sheet1 (at row 1 you have headings)

Now this range is your Table Array. To make this range dynamic, please do the following:


Press ctrl+F3 from key board to get NameManger window


At name you write DynamicRange (you acn write anything here according to your wish)

At refer to box write: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A,0)-1,4)

Note: Since, I assume you have 4 columns, I have written 4 at the end in that formula, you can change it according to the number of columns you have in your data.


Whenever your rowsize of the table will change(shrink or expand), this dynamic namedrange (DynamicRange) will also change accordingly


Now in your vlookup formula, you need to place this dynamic name range(DynamicRange)in table array to fetch the data you want.


Hope this helps.


Regards,

Kaushik

 

[Hui]

Marc


Are you doing this manually or via a VBA script?


If Manually, you can manually select the range quite easily using Ctrl, Shift and Arrow keys


If Via VBA I'd setup a Named Formula which adjusts to the data edges and then apply the VLookup to the named formula

 

[marcvanderpeet12]

Thanks for the solution, this works!