vlookup returns a range

[kendel]

Hello,
I need a formula for vlookup or any formula to return a range. The list is unsorted, also there are duplicates in the list. Please use the same formula in each row.
example:

<TABLE style="WIDTH: 40pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=50><COLGROUP><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 365" span=5 width=10><TBODY><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 8pt; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=7 width=10></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" width=10></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" width=10><TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=17 width=64>jk</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65 width=64>1</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65 width=64>2</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65 width=64>3</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65 width=64>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=17>jj</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>2</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>2</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>1</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=17>af</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>3</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>3</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>7</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=17>jj</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>2</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>3</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>4</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=17>jj</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>2</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>3</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>4</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=17></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7; mso-ignore: colspan" height=17 colSpan=2>need to return</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=17></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=17>jj</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>2</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>2</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>1</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=17>jj</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>2</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>3</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>4</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=17>jj</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>2</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>3</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>4</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl65>2</TD></TR></TBODY></TABLE>
thank you

</TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" width=10></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 8pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" width=10></TD></TR><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=7></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD></TR><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=7></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD></TR><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=7></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD></TR><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=7></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD></TR><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=7></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD></TR><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7; mso-ignore: colspan" height=7 colSpan=5></TD></TR><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=7></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD></TR><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=7></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD></TR><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=7></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD></TR><TR style="HEIGHT: 5.25pt; mso-height-source: userset" height=7><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; HEIGHT: 5.25pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" height=7></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7"></TD></TR></TBODY></TABLE>

 

[Tom Urtis]

It looks like instead of a formula what you are asking for is the same effect that an AutoFilter would show you. For example if you filter (supposedly) column A for jj you would see the same set of data you indicate in your set of expected results. Have you tried filtering, and if not, why would you not want that to be teh way you approach the solution?

 

[kendel]

Hi, thanks for the response, I'll look into filtering. Seems that's something you do to the worksheet, and in this case that's not possible, the idea is to summerize the differnt worker's metrics on a seperate sheet, kinda hardwired.

 

[Tom Urtis]

If you want it on a separate sheet, then do the filter, hit F5 > Special > Visible cells only > Ctrl+C > activate destination sheet > activate cell at top left corner of destination range > Ctrl+V > Esc > go back to original sheet and exit filter mode.

 

[Peter_SSs]

... the idea is to summerize the differnt worker's metrics on a seperate sheet, kinda hardwired.

If you want a formula approach, here is one way that you could try to adapt to your layout.

If you wanted a macro approach, then this thread may be some help.

 

[kendel]

Thank you both very much. I'll use the formula approach, I like it best because after setup nothing else needs be done.
Is a Vlookup that returns a range impossible?
thanks again,
Kendel

 

[Peter_SSs]

Thank you both very much. I'll use the formula approach, I like it best because after setup nothing else needs be done.
Is a Vlookup that returns a range impossible?
thanks again,
Kendel

I'm not quite sure what you mean by "Vlookup returns a range". VLOOKUP is a function that can be placed in a formula in a cell. That formula can only return a value to that cell, not to a range of cells.

You could have a series of VLOOKUP formulas to return different column values from a table, but it doesn't easily lend itself to finding multiple occurrances of the same value in the left hand column of a lookup table.

Also, if you have a large lookup table being referenced by a VLOOKUP, then changing any value in the table will cause the VLOOKUP formula to recalculate even if the change wasn't in either of the columns being used by the VLOOKUP. This can have a noticeable impact on sheet performance.

 

[markmzz]

Another way (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">jk</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style=";">jj</td><td style=";">jj</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">jj</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">jj</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">af</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">7</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">jj</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">jj</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">jj</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1:L1</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$1:1</font>)>COUNTIF(<font color="Red">$A$1:$A$5,$G$1</font>),"",INDEX(<font color="Red">$A$1:$E$5,LARGE(<font color="Green">(<font color="Purple">$A$1:$A$5=$G$1</font>)*(<font color="Purple">ROW(<font color="Teal">$A$1:$A$5</font>)-ROW(<font color="Teal">$A$1</font>)+1</font>),ROWS(<font color="Purple">$1:1</font>)</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[kendel]

Thank you all for your help! Peter's solution works like I needed, but I've learned there's usually more than one way.
Kendel

 

[kendel]

Hello again,
I just wanted to thank you all again for the help on the issue.
I've been able to adapt both the array and helper cell methods. The filter as I'm sure you'd agree is not very elegant , but thanks for the suggestion and the effort to help.
I'm not sure yet, I'll need to do the full year's data, but I would guess the array formula method would calculate faster, so opening and saving the workbook will be faster.
Such an excellent site!
Thanks again,
Kendel