Populate column for list of towns

Hi

Sorry I am new to using excel, can you please explain to me what the line:
=vlookup(A3,$x$1:$y$100,2,false)
means and where I change the values?

Say Cheltenham is in A2 and I want Glouctershire is in B2

So If I input cheltenham anywherein the A column, it will update corresponding B column with Gloucestershire

and the same for London in A3
Leeds in A4

Thanks, your assistance will be appreciated

Hi kingjasonwill,

If you have the lookup table set up as mentioned in the previous post, then you put that formula in the place you want it to show the region.

So if A2 is Cheltenham then you put the Vlookup formula in B2. The formula then scans the list for Cheltenham, and returns the corresponding entry from column 2.

Are the locations you need to place regions aganst unique or duplicated throughout the record sets?

Hi

thanks for the reply.

Yes they are duplicated throughout.

e.g. a list such as:


london
cheltenham
london
london
cheltenham
leeds
leeds
cheltenham
glasgow
glasgow

I have tried the formula and it works but it brings up a #N/A message if the names are duplicated?

Can you help or guide me as to what I am doing wrong please?

Your lookup table should include each name and area only once. Also if you are populating the formula with autofill what your range, as it will move it down automatically.

So if your lookup table has london on top, and your initial range was A1:B2 in the formula and you autofill, range in the second formula will be A2:B3, so you need to make the range an absolute reference so that it still sees London at the top.

Cheers,
Justin

OK, Thanks, But I am a little confused, sorry.

I have included the file I am working on, I have simply created votes data.

Can you please check it and see what is wrong please?

Yes, that makes it a million times clearer, thanks million for your help

Hi

Just one more thing, I have tried the solution, I was wondering can you please tell me how to ensure if my lookup value is not in the lookup list, i.e. no corresponding region, the field doesn't display #N/A and can just be blank?

Thanks in advance

Hi

Also

I would like to save the excel document as a CSV file to import into SQL.
How can I clean up the worksheet to show only the values from the formulas not the actual formula?

Hi

Thanks

Here is an attachment of the file I am working on

At the moment, 78 does not match any corresponding values in the lookup table. This may be the case when I polute all data. So I would like to know how to make sure the #N/A does not show up in D7 on the "voters" worksheet.

Also if I was to save the file as a CSV file to import into my phpmyadmin, how do I clean up table so it can show final values only in voters worksheet so I can delete the "Party Codes" worksheet.

Many thanks well in advance

Can you help please?

hi

when i say I would like it to not show up the #N/A value, I mean is it possible for it to remain blank if there is no value to look up in the "Party Codes" table

Thanks

Can you help please?

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.