Lookup and Reference more than 1 Row of Data

What would your formula be for the first occurrence? Is your lookup table sorted by Ref ID?

provide some sample data.
how would you tell which rows should go together?

The Reference Data would not be sorted in order, as it could be added to.
I'm not sure what my formula would be, from a simplistic point of view a Countif would tell me how many formulas I need, I could then just have a formulas from rows 5-25 (the maximun to be looked up would be 20) so I could just set a list 1-20 (In Col A) and use if Countif<=A1(etc) then Vlookup RefID, REFData. But I don't know how to Affect the Range of the VLookup (i.e. instead of the Range reading A1:B100 it reads A&Range1:B100 - Range1 being a named range which shows the number 10 for instance)
There may be a simpler way, hopefully.

Thanks.

Sample Data.

Sheet = Data

A B
Ref Name
217 Bill
218 Steve
217 Gary
217 Tony

Sheet = LookupSheet

Using the magic formula's when i input 217 into a specific cell a list will appear with
Bill
Gary
Tony

Only will have a Maximun of 20 Names.

Hoep this helps.

Hi Everyone, found the answer (includes code, but as a function - so it's not called or ran to access Recordset (ADO) which would be the easiest but not allowed)
Something called VLOOKUPNth and you declare what one you want to lookup (2nd, 3rd, 4th etc) Function is below.

Function VLOOKUPNTH(lookup_value, table_array As Range, _
col_index_num As Integer, nth_value)
' Extension to VLOOKUP function. Allows for finding
' the " nth " item that matches the lookup value.

Dim nRow As Long
Dim nVal As Integer
Dim bFound As Boolean
VLOOKUPNTH = "Not Found"
With table_array
For nRow = 1 To .Rows.Count
If .Cells(nRow, 1).Value = lookup_value Then
nVal = nVal + 1
End If
If nVal = nth_value Then
VLOOKUPNTH = .Cells(nRow, col_index_num).Text
Exit Function
End If
Next nRow
End With
End Function

Here is this. This is a very useful formula.
HTH
Mike Szczesny

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">A</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">B</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">C</TD><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">D</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">1</TD><TD>Ref</TD><TD>Name</TD><TD> </TD><TD style="TEXT-ALIGN: right">217</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">2</TD><TD style="TEXT-ALIGN: right">217</TD><TD>Bill</TD><TD> </TD><TD>Bill</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">3</TD><TD style="TEXT-ALIGN: right">218</TD><TD>Steve</TD><TD> </TD><TD>Gary</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">4</TD><TD style="TEXT-ALIGN: right">217</TD><TD>Gary</TD><TD> </TD><TD>Tony</TD></TR><TR style="HEIGHT: 18px"><TD style="BORDER-BOTTOM: #f0f0f0 3px solid; TEXT-ALIGN: center; BORDER-LEFT: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; BORDER-TOP: #f0f0f0 3px solid; BORDER-RIGHT: #f0f0f0 3px solid">5</TD><TD style="TEXT-ALIGN: right">217</TD><TD>Tony</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>{=INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($D$2:D2)))}</TD></TR><TR><TD>D3</TD><TD>{=INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($D$2:D3)))}</TD></TR><TR><TD>D4</TD><TD>{=INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($D$2:D4)))}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>