Post Code search

  • Thread starter Thread starter Porirua
  • Start date Start date
P

Porirua

Hi everyone,
I am new to excel and have been given a task which is currently beyond
my capabalities and seek help from all you experts out there.
I have about 12000 postcodes in column A and columns B to K contain
miscallaneous information relating to each postode, ie name, address,
tel, fax etc
What I would like is help with a formula whereby I would type in the
postcode and the information would search and display the details in
columns B to K for that particular postcode.
I am aware the easiset option is to use the edit and find function,
however ideally I would like to use sheet two for the search.
I would appreciate any help, thanks in advance
Porirua
 
Porirua said:
Hi everyone,
I am new to excel and have been given a task which is currently beyond
my capabalities and seek help from all you experts out there.
I have about 12000 postcodes in column A and columns B to K contain
miscallaneous information relating to each postode, ie name, address,
tel, fax etc
What I would like is help with a formula whereby I would type in the
postcode and the information would search and display the details in
columns B to K for that particular postcode.
I am aware the easiset option is to use the edit and find function,
however ideally I would like to use sheet two for the search.
I would appreciate any help, thanks in advance
Porirua
Click to expand...


Let's assume that your table is in Sheet1 and you will enter your post
code in Cell A2, Sheet 2. Let us further assume that your table range
(in sheet 1) is A2:K12000.

With the above assumptions, here are the formulas that you will enter
in the following cells in Sheet 2:

B2 : =vlookup(A2,Sheet1!$A$2:$K$12000,2,0)
C2: =vlookup(A2,Sheet1!$A$2:$K$12000,3,0)
D2: =vlookup(A2,Sheet1!$A$2:$K$12000,4,0)
E2: =vlookup(A2,Sheet1!$A$2:$K$12000,5,0)
F2: =vlookup(A2,Sheet1!$A$2:$K$12000,6,0)
G2: =vlookup(A2,Sheet1!$A$2:$K$12000,7,0)
H2: =vlookup(A2,Sheet1!$A$2:$K$12000,8,0)
I2: =vlookup(A2,Sheet1!$A$2:$K$12000,9,0)
J2: =vlookup(A2,Sheet1!$A$2:$K$12000,10,0)
K2: =vlookup(A2,Sheet1!$A$2:$K$12000,11,0)

Hope this is what you are looking for.

Regards.
 
Thankyou Benjielop, you formula is perfect, I will play around with it
to reflect different cells in sheet 2. The following question is not
important, however if you could assist me on the following.
If I input an incorrect post code understandibly I will get error #n/a,
how could I amend formula to show some brief text to state postcode not
found?
Once again thank you for your time
Porirua
 
=if(iserror(your vlookup formula),"Incorrect Post Code Entered",your
vlookup formula)

OR you can replace "Incorrect Post Code Entered" with any comments that
you want (as long as they are inside the quotes).

Regards.
 
B2 : =vlookup(A2,Sheet1!$A$2:$K$12000,2,0)
C2: =vlookup(A2,Sheet1!$A$2:$K$12000,3,0)
D2: =vlookup(A2,Sheet1!$A$2:$K$12000,4,0)
E2: =vlookup(A2,Sheet1!$A$2:$K$12000,5,0)
F2: =vlookup(A2,Sheet1!$A$2:$K$12000,6,0)
G2: =vlookup(A2,Sheet1!$A$2:$K$12000,7,0)
H2: =vlookup(A2,Sheet1!$A$2:$K$12000,8,0)
I2: =vlookup(A2,Sheet1!$A$2:$K$12000,9,0)
J2: =vlookup(A2,Sheet1!$A$2:$K$12000,10,0)
K2: =vlookup(A2,Sheet1!$A$2:$K$12000,11,0)
Click to expand...

Just a thought ..

Might be a little easier to just fill across from the starting cell's
vlookup in B2 by using an incrementer formula* (for copying across) for the
col_index_num instead of hardcoding it in this instance

Put in B2:
=VLOOKUP($A2,Sheet1!$A$2:$K$12000,COLUMNS($A$1:B1),0)
and then copy B2 across to K2, fill down as required

*COLUMNS($A$1:B1) will evaluate to: 2
and when copied across, return: 3,4,5,6, ... in successive columns
 
Max said:
Just a thought ..

Might be a little easier to just fill across from the starting cell's
vlookup in B2 by using an incrementer formula* (for copying across) fo
the
col_index_num instead of hardcoding it in this instance

Put in B2:
=VLOOKUP($A2,Sheet1!$A$2:$K$12000,COLUMNS($A$1:B1),0)
and then copy B2 across to K2, fill down as required

*COLUMNS($A$1:B1) will evaluate to: 2
and when copied across, return: 3,4,5,6, ... in successive columns
Click to expand...

Indeed it is easier to do this but since the OP is new at Excel,
thought it was more appropriate to enter the individual hard code
formulas to instill the basic concept of VLOOKUP.


Regards
 
:
....
Indeed it is easier to do this but since the OP is new at Excel, I
thought it was more appropriate to enter the individual hard coded
formulas to instill the basic concept of VLOOKUP.
Click to expand...

Agreed in principle <g>. Albeit it might be a little tough at times for
responders to draw the line where it is felt the core question(s) posed has
been sufficiently dealt with. Cheers.
 
Back
Top