Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
=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 |
#5
|
|||
|
|||
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 ---- |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
"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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
post code on col1 & list of suburbs in col2 - what is the formula | Excel Discussion (Misc queries) | |||
Post code & Suburbs | Excel Discussion (Misc queries) | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Code Post: Extract Trendline coefficients | Excel Discussion (Misc queries) |