look up and display values from another worksheet based

[squire82]

Using excel 2003

Apologies if the title isn't great as I'm not 100% certain which formula will work best for this.

Worksheet 1

<TABLE style="WIDTH: 259pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=346 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3185" width=90><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" width=64 height=18>A</TD><TD style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>B</TD><TD style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>C</TD><TD style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 48pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=64>D</TD><TD style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 67pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=90>E</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>Week1</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Pension</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Life</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">total</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Customer</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18 x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>1000</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>900</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num x:fmla="=B3+C3">1900</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Gumbo</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18 x:num>1</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>1000</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>800</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num x:fmla="=B4+C4">1800</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Skinner</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>1000</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>700</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num x:fmla="=B5+C5">1700</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Hubbert</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>1000</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>600</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num x:fmla="=B6+C6">1600</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Van Houghton</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18 x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>1000</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>500</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num x:fmla="=B7+C7">1500</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Jones</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18 x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>1000</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>400</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num x:fmla="=B8+C8">1400</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Burns</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18 x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>1000</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>300</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num x:fmla="=B9+C9">1300</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Simpson</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18 x:num>5</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>1000</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>200</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num x:fmla="=B10+C10">1200</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Montgomery</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18 x:num>13</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>1000</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>100</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num x:fmla="=B11+C11">1100</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Sizlak</TD></TR></TBODY></TABLE>

Worksheet 2

<TABLE style="WIDTH: 118pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=157 border=0 x:str><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3072" width=86><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2531" width=71><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 65pt; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" width=86 height=18>A</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; WIDTH: 53pt; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" width=71>B</TD></TR><TR style="HEIGHT: 13.2pt" height=18><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; HEIGHT: 13.2pt; BACKGROUND-COLOR: transparent" height=18>Week Saught</TD><TD class=xl24 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">VARIABLE</TD></TR></TBODY></TABLE>

I'm looking for a formula that will output all the sales from worksheet 1 based on the value i place in cell B2 on worksheet 2.

So if I were to input 6 in B2 the following would be displayed:

<TABLE style="WIDTH: 259pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=346 border=0 x:str><TBODY><TR style="HEIGHT: 13.2pt" height=18><TD class=xl22 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>1000</TD><TD class=xl22 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num>100</TD><TD class=xl22 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent" x:num x:fmla="=B10+C10">1100</TD><TD class=xl22 style="BORDER-RIGHT: #e0e0e0; BORDER-TOP: #e0e0e0; BORDER-LEFT: #e0e0e0; BORDER-BOTTOM: #e0e0e0; BACKGROUND-COLOR: transparent">Sizlak</TD></TR></TBODY></TABLE>

Thanks in advance.

 

[GlennUK]

I don't see how 6 relates to
1000 100 1100 Sizlak
... what logic have you used?