Post Code search
 

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
------------------------------------------------------------------------
Porirua's Profile: http://www.excelforum.com/member.php...o&userid=24505
View this thread: http://www.excelforum.com/showthread...hreadid=381022

Porirua Wrote:
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


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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=381022

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


--
Porirua
------------------------------------------------------------------------
Porirua's Profile: http://www.excelforum.com/member.php...o&userid=24505
View this thread: http://www.excelforum.com/showthread...hreadid=381022

=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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=381022

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)

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Max Wrote:
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)

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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.


Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=381022

"BenjieLop" wrote:
....
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.

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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----