Create Database Type Lookup In Excel

[powlaz]

I really didn't know how to title this post. Anyway I have a spreadsheet
with every zipcode in the state in column A. In column B I have a list of
towns associated with each zipcode. Rather then have the user press Ctrl-F
to navigate through the 800 row list of zipcodes I was hoping to build a
little dialog box that allows them to enter the zipcode and then returns the
town.

Here's the problem, I don't know how to do this or where to start. Ideally
I'd like the only thing they see to be the little zipcode lookup box (vs
opening a whole spreadsheet). Please tell me how to get this going.

Thanks,
MJ

 

[Bernard Liengme]

Have a look at Vlookup in Help and in the sites below
Please come back if more is needed

http://www.officearticles.com/excel/vlookup_formulas_in_microsoft_excel.htm
http://www.contextures.com/xlFunctions02.html


best wishes

 

[מיכ×ל (מיקי) ×בידן]

In order to leave it as simple as it goes - go to a new sheet and use 2 cells.
In one type the ZIP code - un the other use a VLOOKUP function in order to
return the town.turn
Micky

 

[Pete_UK]

It's well worth following the links that Bernard has provided, in
order to learn more.

However, you probably just want a solution for now, so first of all
Insert a new worksheet in the file. You can use this to allow your User
(s) to enter a zip code and then return the appropriate town.
Consequently, the sheet that contains the list of zip codes (which I
shall assume is called Sheet1) can be hidden from view, so that your
Users do not get too curious and explore what you have in the file.
Type a message like this in A1 of the new sheet:

Enter a zip-code:

(you might need to widen the column) and then B1 will be used for the
zipcode. You might want to highlight this in bright yellow, and put a
thick border around it to focus the attention of your Users on that
cell.

In compiling the formula that returns the town, which will go in cell
C1, you need to consider two special situations:

1 - the cell B1 may be empty (as it is now), so your formula should
account for this (by making C1 look empty also);

2 - the User might type something into B1 which is not recognised as a
zip-code by your formula. Rather than showing an error in C1, your
formula should detect this and return some suitable error message.

Another consideration is the formatting applied to your zipcodes in
the reference sheet - are these numbers or are they text values which
look like numbers? I'm going to assume the latter, so you can format
cell B1 to Text also.

So, this is the formula that you will need to put in C1:

=IF(B1="","",IF(ISNA(MATCH(B1,Sheet1!A:A,0)),"Not recognised",VLOOKUP
(B1,Sheet1!A:B,2,0)))

Now, if B1 is empty, then C1 will also look empty, but if you put a
valid zipcode in B1 you should see the town shown in C1. If B1 is not
found in the reference table then C1 will show the message "Not
recognised".

If this doesn't happen for you, then the format of your zip codes must
be different, so post a few examples of your data here.

Hope this helps.

Pete

 

[trip_to_tokyo]

I have just uploaded a brief worked example for you at:-

http://www.pierrefondes.com/

Take a look at the first item on the home page (item number 23).

All you have to do is to enter the zip code in cell A2 and the town will be
returned in cell B2.

You need, of course, to adapt this example to your needs.

If my comments have helped please hit Yes.

Thanks,.