General discussion

Locked

Excel 2000 Lookup

By Softbott ·
G'day all, I have a file (PostCodes) in which column A has the postcode and column B the city. I a different spreadsheet (customers) I want to enter the postcode and have the city value entered directly into the adjacent cell. Thankyou Harry.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel 2000 Lookup

by JimBb In reply to Excel 2000 Lookup

G'day mate,
Just supposing:
-postcodes.xls has postcodes in column A and cities in B, the first 50 rows, on sheet1.
-customers.xls has an input cell for the postcode in A1, and the city should end up in B1.
B1 could contain:
=VLOOKUP(A1;[postcodes.xls]Sheet1!$a$1:$b$50;2;TRUE)

-A1 refers to which cell's contents you want to use to look up the correct city.
-[postcodes.xls]Sheet1! refers to the tab "Sheet1" in the file "postcodes.xls".
-$a$1:$b$50 refer to the range in which you are looking for a city. The $ signs are not required, but they're easy for copying the formula around.
-2 refers to "the data I retrieve comes from the second column from my range"
-TRUE (not obliged to use this) indicates if you want "the nearest value" or not if a posctode is entered that is not in your list.

One more thing: your table should be sorted on the postcode.

Jim

Collapse -

Excel 2000 Lookup

by JimBb In reply to Excel 2000 Lookup

Forgot: the semicolons in my formula could be commas for you, they depend on your regional settings in Windows.

Jim

Collapse -

Excel 2000 Lookup

by Softbott In reply to Excel 2000 Lookup

Poster rated this answer

Collapse -

Excel 2000 Lookup

by DKlippert In reply to Excel 2000 Lookup

Answer 1 is correct, but I would use FALSE as the last argument. This will cause the VLOOKUP formula to return NA if it cannot find an exact match. You don't want the next closest code if it's going to send your parcel to Alice Springs instead of Adelaide!
Another thing to consider is that the Postal codes need to be sorted in ascending order.

Collapse -

Excel 2000 Lookup

by Softbott In reply to Excel 2000 Lookup

Poster rated this answer

Collapse -

Excel 2000 Lookup

by Softbott In reply to Excel 2000 Lookup

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums