Newbie Question for VLOOKUP and Text vs. Numbers

[Dev745]

I'm fairly new to excel, at least for using it more than just cataloguing my CD collection.

I'm using a VLOOKUP function to find a name in another worksheet, and then provide the zip code from that worksheet. The problem for me is that the cells are formatted as zip codes and the zip codes are from the east coast which means they start with 0. When I complete the function I only get the last 4 digits of the zip code. Can anybody help me? A similar problem happens when I try to VLOOK for their birthdate, some strange numbers come over. Thanks for any help, and if I shouldn't be wasting you power users' time, please let me know that too. Thanks! Dev

 

[Thorin]

Welcome to the board.

Don't worry if you think the question is too simple, the people here are happy to help in any way they can.

As a Brit, the different zip codes in the US are a bit confusing, so forgive me if I have mis-understood your question.

Are the zip codes in question a 5 digit code, and you want to replicate this, if so then would this help you:-

=TEXT(VLOOKUP(F9,B3:C9,2,FALSE),"00000")

 

[Brian from Maui]

I'm fairly new to excel, at least for using it more than just cataloguing my CD collection.

I'm using a VLOOKUP function to find a name in another worksheet, and then provide the zip code from that worksheet. The problem for me is that the cells are formatted as zip codes and the zip codes are from the east coast which means they start with 0. When I complete the function I only get the last 4 digits of the zip code. Can anybody help me? A similar problem happens when I try to VLOOK for their birthdate, some strange numbers come over. Thanks for any help, and if I shouldn't be wasting you power users' time, please let me know that too. Thanks! Dev

The cell that houses the formula should also be formatted the same as the cells that house the zip codes in your lookup table. As for the dates, what you're seeing is the actual number of days since 1/1/1900. Again format the cell that houses the formula as a date.

 

[Dev745]

THANK YOU THANK YOU

Thank you so much! I was up til 3am this morning, and you helped just like that. The world is kind!

 

[Thorin]

Right click on the cell, and select format cells, then select date from the list and choose whatever date format you wish to show.

 

[Dev745]

The help on this board.

I love this board! Everyone is so helpful and quick to help. Thank you again, I've used excel for years to do mundane things, recently I've started using it for work and it is an amazing program.