Dynamic VLOOKUP

[whitey1987]

Hello everyone,

Hoping one of you can help me out.

I have a VLOOKUP in my worksheet, but the range that I'm looking up needs to be dynamic.

In other words the amount of rows may change, for example the range I'm looking up one month may be A1:Z10 but next month it could be A1:Z20. Is there a way I can account for this, without having to change the formula every time I run it?

Many thanks in advance.

 

[Jonmo1]

Welcome to the board...

The easiest way is to use a range that is moderately larger than you ever expect the data to be...

If you're pretty sure it will never be more than 1000 rows, then use
A1:Z1500 or similar...


Again, that's easiest...not necessarily most ideal..


Look into dynamic named ranges, this would probably be best..


Hope that helps.

 

[TIV73]

You could try referencing your range without rows at all, using only the rows like A:Z.

 

[RamaRall]

I never use the row number. I always use the columns like A:Z. That should work for you...hopefully

 

[whitey1987]

Thanks for the responses guys, really appreciate it.

If for example I want to return values that are in one column, can I just say 'Z' rather than Z1:Z100 for example?

 

[Arovin]

Just Z probably wont work, use Z:Z instead.

 

[snoopyhr]

take a look at this link, might get you started ....

http://www.youtube.com/watch?v=oNPVNc8cze0&NR=1