Newbie Question for VLOOKUP and Text vs. Numbers

Dev745

New Member
Joined
Jul 25, 2007
Messages
3
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Thorin

Board Regular
Joined
Feb 8, 2007
Messages
243
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:-

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

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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

New Member
Joined
Jul 25, 2007
Messages
3
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

Board Regular
Joined
Feb 8, 2007
Messages
243
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

New Member
Joined
Jul 25, 2007
Messages
3
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.
 

Forum statistics

Threads
1,181,102
Messages
5,928,069
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top