dynamic VLookup

**michaelk68** · 04-10-2009, 02:27 PM

Hello - I am working on a small file that will compare email addresses from column A and Column B. If the email address from Column B matches, the formula will show "Yes" and if not, "No".

HOWEVER, I wish to include this in a macro and the two lists will always be of varying lengths.

Is there a way in which I can 'tell' excel how long each list is, or have it run the vlookup and erase/clear those columns w/o data in them?

Any advice/links to previously-written assistance will be much appreciated.

-MK

**franciz** · 04-10-2009, 02:33 PM

Hi

Are you using Vlookup? if yes, what is not working
pls show your formula

cheers, francis

**arthurbr** · 04-10-2009, 03:21 PM

Hi and welcome to the board

Is there a way in which I can 'tell' excel how long each list is, or have it run the vlookup and erase/clear those columns w/o data in them?

To solve this part of your question,, have a look at Dynamic ranges

**michaelk68** · 04-10-2009, 04:18 PM

Merci, Arthur.

Another ? - Here is my vlookup

=IF(ISNA(VLOOKUP(A2,$D$2:$D$185,1,FALSE)),"No","Yes")

How can I replace the "185" after the D with the dynamically-created value of a cell ?

**martindwilson** · 04-10-2009, 06:25 PM

you can use indirect
e.g.
=VLOOKUP(A2,INDIRECT("D2:"&E1),1,FALSE) where e1 contains D185
or
=VLOOKUP(A2,INDIRECT("D2:D"&E1),1,FALSE) where e1 contains 185
but if you are using a macro thats probably unnecessary ther are diferent methods for dynamic ranges.