Return a second and third match value using vlookup

[ensmith]

I am trying to capture the second and third, etc values when using a vlookup to retrieve the results from a pivot with the same Pos# (see below). Looking for a formula that will bring back the values. Thanks


Return values here:[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl65, width: 64"]NAME[/TD]
[TD="class: xl65, width: 64"]TITLE[/TD]
[TD="class: xl65, width: 64"]HIREDT[/TD]
[TD="class: xl65, width: 64"]HRS[/TD]
[TD="class: xl65, width: 64"]SHIFT[/TD]
[TD="class: xl65, width: 64"]FTE[/TD]
[/TR]
</tbody>[/TABLE]



Retrieve values from here:
[TABLE="width: 598"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]POS#[/TD]
[TD]NAME[/TD]
[TD]TITLE[/TD]
[TD]HIREDT[/TD]
[TD]HRS[/TD]
[TD]SHIFT[/TD]
[TD]FTE[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Unorben,A[/TD]
[TD]xxxx1[/TD]
[TD="align: right"]1/12/12[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Howard,Glen[/TD]
[TD]xxxx2[/TD]
[TD="align: right"]1/13/12[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Jones,L[/TD]
[TD]xxxx3[/TD]
[TD="align: right"]1/14/12[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Mckenzie,H[/TD]
[TD]xxxx4[/TD]
[TD="align: right"]1/15/12[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Griffiths,Marie[/TD]
[TD]xxxx5[/TD]
[TD="align: right"]1/16/12[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Hernandez,S[/TD]
[TD]xxxx6[/TD]
[TD="align: right"]1/17/12[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Herd, joe[/TD]
[TD]xxxx7[/TD]
[TD="align: right"]1/17/12[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Shirley, larry[/TD]
[TD]xxxx8[/TD]
[TD="align: right"]1/17/12[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Hern, don[/TD]
[TD]xxxx9[/TD]
[TD="align: right"]1/17/12[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 598"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

So I copied your data into a new sheet in A1:G10. In I5, I have the Pos# that you are looking up. For example, 22. Then in J5, I have the number of the total matches that you want to return. So, If I5 has 22, and J5 has 3, it will return Shirley, larry.

This is an array formula, so you have to hit control+shift+enter after entering the formula.

=LOOKUP(SMALL(IF($A$2:$A$10=$I$5,ROW($A$2:$A$10)),$J$5),ROW($A$2:$A$10),$B$2:$B$10)