Display records on different sheets using vlookup

Hi,

Welcome to MrExcel.

Is this the type of data you have on Sheet1 and the result you expect on Sheet2?...







You can change the sheet name and cell references to suit, but you MUST enter the formula in C2 with ctrll-shift-enter not just enter. You can then copy the formula across and down as far as you require.

Good luck

Ak

Maybe a pivot table could work?

<TABLE style="WIDTH: 133pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=177><COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 69pt; BACKGROUND: #dbe5f1; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-pattern: #DBE5F1 none" height=20 width=92>Age</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 64pt; BACKGROUND: #dbe5f1; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-pattern: #DBE5F1 none" class=xl65 width=85>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>Row Labels</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; BORDER-RIGHT: #f0f0f0; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Sum of Mark</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>Bob</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>80</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>Joe</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>75</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>Kim</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>75</TD></TR></TBODY></TABLE>

Hi Akashwani,
I have performed this formula but unfortunately i got empty cell. I am providing more details. I have sheet1 values are:
<table width="257" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4717;width:97pt" width="129"> <col style="width:48pt" width="64"> <tbody><tr style="mso-height-source:userset;height:28.5pt" height="38"> <td class="xl65" style="height:28.5pt;width:48pt" width="64" height="38">Pcode</td> <td class="xl65" style="width:97pt" width="129">Locality</td> <td class="xl65" style="width:48pt" width="64">State</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0800</td> <td>DARWIN</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0810</td> <td>TIWI</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0810</td> <td>WAGAMAN</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0810</td> <td>WANGURI</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0812</td> <td>NORTHLAKES</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0812</td> <td>SANDERSON</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0812</td> <td>WOODLEIGH GARDENS</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0812</td> <td>WULAGI</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0820</td> <td>THE NARROWS</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0820</td> <td>WINNELLIE</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0820</td> <td>WOOLNER</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0828</td> <td>BERRIMAH</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0828</td> <td>KNUCKEY LAGOON</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0829</td> <td>HOLTZE</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0829</td> <td>PINELANDS</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0830</td> <td>ARCHER</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0830</td> <td>DRIVER</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0830</td> <td>DURACK</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0830</td> <td>FARRAR</td> <td>NT</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">0830</td> <td>GRAY</td> <td>NT</td> </tr> </tbody></table>

I have sheet2 where i want to display selected records based on Pcode when user eneter Postcode in cell and press enter and that will display corresponding Pcode below. Colums names are different on both sheets. Below is the sheet2 look

<table width="287" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:5668;width:116pt" width="155"> <col style="mso-width-source:userset;mso-width-alt:4827;width:99pt" width="132"> <tbody><tr style="mso-height-source:userset;height:27.75pt" height="37"> <td class="xl66" style="height:27.75pt;width:116pt" width="155" height="37"> <table width="287" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:5668;width:116pt" width="155"> <col style="mso-width-source:userset;mso-width-alt:4827;width:99pt" width="132"> <tbody><tr style="mso-height-source:userset;height:27.75pt" height="37"> <td class="xl66" style="height:27.75pt;width:116pt" width="155" height="37"> <table width="662" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:19382;width:398pt" width="530"> <col style="mso-width-source:userset;mso-width-alt:4827;width:99pt" width="132"> <tbody><tr style="mso-height-source:userset;height:27.75pt" height="37"> <td class="xl66" style="height:27.75pt;width:398pt" width="530" height="37">enter a postcode</td> <td style="width:99pt" width="132">
</td> </tr> <tr style="mso-height-source:userset;height:47.25pt" height="63"> <td class="xl65" style="height:47.25pt" height="63">0810 (cell where user will enter value and press enter)</td> <td valign="top" align="left"> <table cellpadding="0" cellspacing="0"> <tbody><tr> <td style="height:47.25pt;width:99pt" width="132" height="63">
</td> </tr> </tbody></table> </td> </tr> <tr style="mso-height-source:userset;height:26.25pt" height="35"> <td class="xl67" style="height:26.25pt" height="35">Suburb</td> <td class="xl67" style="border-left:none">State</td> </tr> <tr style="mso-height-source:userset;height:23.25pt" height="31"> <td class="xl68" style="height:23.25pt;border-top:none" height="31">WANGURI</td> <td class="xl68" style="border-top:none;border-left:none">NT</td> </tr> <tr style="mso-height-source:userset;height:31.5pt" height="42"> <td class="xl68" style="height:31.5pt;border-top:none" height="42">(More records will be filled here)</td> <td class="xl68" style="border-top:none;border-left:none"> </td> </tr> </tbody></table></td> <td style="width:99pt" width="132">
</td> </tr> <tr style="mso-height-source:userset;height:47.25pt" height="63"> <td class="xl65" style="height:47.25pt" height="63">Please help with vlookup formula. I have tried but no success.
</td> <td valign="top" align="left">
</td> </tr> <tr style="mso-height-source:userset;height:26.25pt" height="35"> <td class="xl67" style="height:26.25pt" height="35">
</td> <td class="xl67" style="border-left:none">
</td> </tr> <tr style="mso-height-source:userset;height:23.25pt" height="31"> <td class="xl68" style="height:23.25pt;border-top:none" height="31">
</td> <td class="xl68" style="border-top:none;border-left:none">
</td> </tr> <tr style="mso-height-source:userset;height:31.5pt" height="42"> <td class="xl68" style="height:31.5pt;border-top:none" height="42">
</td> <td class="xl68" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17">
</td> <td>
</td> </tr> </tbody></table></td> <td style="width:99pt" width="132">
</td> </tr> <tr style="mso-height-source:userset;height:47.25pt" height="63"> <td class="xl65" style="height:47.25pt" height="63">
</td> <td valign="top" align="left">
</td> </tr> <tr style="mso-height-source:userset;height:26.25pt" height="35"> <td class="xl67" style="height:26.25pt" height="35">
</td> <td class="xl67" style="border-left:none">
</td> </tr> <tr style="mso-height-source:userset;height:23.25pt" height="31"> <td class="xl68" style="height:23.25pt;border-top:none" height="31">
</td> <td class="xl68" style="border-top:none;border-left:none">
</td> </tr> <tr style="mso-height-source:userset;height:31.5pt" height="42"> <td class="xl68" style="height:31.5pt;border-top:none" height="42"> </td> <td class="xl68" style="border-top:none;border-left:none"> </td> </tr> </tbody></table>

Hi,

Which version of Excel are you using?
Do you have any blank cells in your range?

Ak

I am using Excel 2007.

There is no empty cell.

I can show you file. I am new to this forum. Don't know how to show file. Suggest some way.

Hi,

Take a look here....

justmine584.xlsx

I have created a Named Table on sheet1 PCodeTable
The formulas on Sheet2 refer to the Named Table using Table Nomenclature.
This means as you add data to the table (rows or columns) the formula will refer to them.

Take a look here....
http://www.youtube.com/user/ExcelIsFun#p/search/0/baWNi_Uye2A

If you have any problems, post your file to the net then I will take a look at it for you.

Good luck

Ak

Hi,

I have updated your Workbook....


justmine584.xlsx


I hope this helps you complete your task.

Good luck

Ak

You did excellent in attached file. But problem is that 0800 postcode have more that one suburb. I want to do it with vlookup. Below is my file. Please help

http://hotfile.com/dl/115636705/3595812/Find_Postcod.zip.html

Hi,

I'll take a look at the file, but I'm sure that you cannot do this with Vlookup as Vlookup will only return the first value that it comes to in the list, it will not return more than that.

What was wrong with the last file link I gave you? On your list 0800 only has Darwin, the same as on mine and I downloaded the full Australian Postcode Database!!!

Ak