need to vlookup on a dynamic list

[aeg42]

Hey guys,

I wrote a macro that does a vlookup on a list of product codes an
pulls the descriptions out. The problem is that people are going to b
constantly adding to that list, so I need the macro to be able to d
the lookup on that column no matter how long it is. Is there a way t
do that?

Right now the macro is hard-coded to rows 2 through 1013. Of course
the lookup function will not be 100% accurate if the list becomes an
longer.

Much thanks in advance,

Andre

 

[Bob Phillips]

Macro? VLOOKUP?

Here is a dynamic VLOOKUP

=VLOOKUP(A1,OFFSET(G1,,,COUNTA(G:G),3),2,FALSE)

Obviously this is a function, but can you incorporate it, and do we need to
do more? If so post the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[gilbert]

Hi Bob,

This is interesting...can you elaborate further on this? will it be th
same if we intend to use MATCH instead of VLOOKUP? How shall we defin
the range of table to lookup? What is COUNTA(G:G)?

I hope you could share with me.

Thank you.

Rgds,
Gilber

 

[Bob Phillips]

Morning Gilbert,

Yes the principle would be the same, as MATCH also compares against a range,
the difference being that VLOOKUP tables are n rows x m columns, whereas
MATCH uses n rows x 1 column. So in that case we would use

=MATCH(A1,OFFSET(G1,,,COUNTA(G:G),1),0)

What is happening in this instance is that OFFSET is being used to define a
range starting at G1, with a height (rows) of the numbers of items in column
G, and a width (columns) of 1. The COUNTA(G:G) counts the non-blank cells in
coilumn G.

Potential difficulties
- blanks in column G - this would cause the height to be less than what is
probably required
- not starting at G1 - this requires knowing how many items between G1 and
the start contain data, invalidatin g this dynamic approach, but it can be
circumvented by defining the range more precisely (COUNTA(G10:G99) say)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[aeg42]

Thanks so much Bob. Worked like a charm