Thread: lookup list

I am trying to get excel to return a list of all vlookup results when there is more than 1 instead of just giving me an N/A result. Can I do that within vlookup or is there another way? See my attached example spreadsheet.

Normally you would have a construction like

HTML Code: 

=INDEX($B$2:$B$16,Match(524210,$E$2:$E$16,0),1)

if it was a single (first) lookup. As it is the Nth I would use

HTML Code: 

=INDEX($B$2:$B$16,NthMatch(524210,$E$2:$E$16,2),1)

Where this is the code

Option Explicit

Function NthMatch(Val, MyRng As Range, Nth As Long) As Long

Dim NthCounter As Long

If MyRng.Cells.Count < 1 Then Exit Function
If MyRng.Columns.Count <> 1 Then Exit Function
If IsEmpty(Val) Then Exit Function
If IsEmpty(Nth) Then Exit Function
If Nth > Application.WorksheetFunction.CountIf(MyRng, Val) Or _
         Application.WorksheetFunction.CountIf(MyRng, Val) = 0 Then Exit Function

For NthMatch = 0 To MyRng.Cells.Count
  If MyRng(NthMatch, 1) = Val Then NthCounter = NthCounter + 1
  If NthCounter = Nth Then Exit Function
Next NthMatch

End Function

On the Ozgrid they tackle it as such:

Function Nth_Occurrence(range_look As Range, find_it As String, _
  occurrence As Long, offset_row As Long, offset_col As Long)

Dim lCount As Long
Dim rFound As Range

  Set rFound = range_look.Cells(1, 1)
    For lCount = 1 To occurrence
      Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)
    Next lCount
    Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function

Your use: =Nth_Occurrence($E$2:$E$16, "524210", 1, 0, -3)

http://www.ozgrid.com/Excel/find-nth.htm

In this type of setup any formula based approach is always going to be messy and is unlikely to offer the same flexibility of a UDF - this is partly down to the layout of the source data.

Golden rule is to avoid repetitive calculations and that will be adopted in below.

If we assume for sake of argument / demo that using the sample file B21 holds code3 of interest: 524210

First - identify the number of records that should be returned in results table:

C21: =COUNTIF($E$1:$E$16,$B21)

In this case that will be 2.

Next - identify the row(s) on which the n instances are to be found:

A23: =IF(ROWS($A$23:A23)>$C$21,"",MATCH(1,INDEX(($E$1:$E$16=$B$21)*(ROW($E$1:$E$16)>N($A22)),0),0))
copied down to A27
if preferred you could use a SMALL Array

(I used 27 to account for possibility of 5 matches - you should adjust as neccessary)

We use A23:A27 to generate the results table (specifically to avoid [expensive] repetitive calcs)

B23: 
=IF($A23="","",LOOKUP(REPT("Z",255),$A$1:INDEX($A:$A,$A23)))
copied down to B27

C23:
=IF($A23="","",INDEX($B:$B,$A23))
copied down to C27

Changing B21 to 531120 you should get 5 records returned