Thread: Populate column for list of towns

Hi

Thanks Can you tell me where I include that line in the following:

=vlookup(A3,$x$1:$y$100,2,false)

or how do I implement that line?

=IF(ISNA(<the vlookupformula>),"",<the vlookupformula>) into my lookup line?

Thanks
Can you help please?

=IF(ISNA(vlookup(A3,$x$1:$y$100,2,false),"",vlookup(A3,$x$1:$y$100,2,false))

thanks, this works, but do you know how i can use this in multiple rows,
I trie copying and pasting but this completely slows down excel and even freezes as there are over 45,000 records.

Can you suggest a way to make it quicker if at all possible?

Thanks in advance

Can you help?

Unfortunately, no, not with a better formula I know of. (2007 has the IFERROR function, but per your profile, you're on 2000.) If you're looking up 45,000 records in 45,000 other records, you're asking Excel to do as many as 2 billion operations. Since you're using the vlookup twice, it can be as many as 4 billion operations. Naturally, it's going to be a bit slow.

You can turn automatic calculation off and that will allow you to make changes to the tables without it recalculating. To update the table, you'd press F9 or turn automatic calculation back on.

A second option is to use conditional formatting to turn the font color the same as the background color if it's #N/A and then use a straight vlookup without the error checking. Of course, the #N/A values will still be in the column then.