# Find second, third matches in Excel lookup



## Chellevis (May 13, 2008)

This is an excerpt from my spreadsheet:

Ashland, KY	CCG	26
Ashland, KY	CHI	23
Ashland, KY	CAG	14
Ashland, KY	DCL	12
Ashland, KY	BAA	8
Ashland, KY	DAH	7
Ashland, KY	BPA	5
Ashland, KY	CCD	5

On a separate sheet, I have a list like this:

Ashland, KY
Claremore, OK
Corinth, MS
Cullman, AL
El Centro, CA
Enterprise, AL


On the second sheet, I want to write a series of lookup statements for each of the entries (i.e. Ashland, KY) that will return the first, second, etc matches for each entry. So, when all is said and done, the results for Ashland, Ky would look like this:

Ashland, KY	CCG	CHI	CAG	DCL

I've tried INDEX and MATCH, but to no avail. If anyone can help me, I would much appreciate it!! If it matters, I am using Excel 2007. Thanks!!


----------



## Chellevis (May 13, 2008)

I figured it out! Thanks, in advance, for anyone looking to solve!


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome.

Glad you solved it - and so quickly!

Would you like to post your solution, in order that others may benefit - just in case they have a similar problem.


----------



## Chellevis (May 13, 2008)

Here's the solution I used:

Function Nth_Occurrence(range_look As Range, LookFor As String, FoundIt As Long, offset_row As Long, offset_col As Long)


Dim Cnt As Long
Dim Result As Range

Set Result = range_look.Cells(1, 1)
For Cnt = 1 To FoundIt
Set Result = range_look.Find(LookFor, Result, xlValues, xlWhole)
Next Cnt
Nth_Occurrence = Result.Offset(offset_row, offset_col)

End Function


----------



## Glaswegian (Sep 16, 2005)

Nice Function - thanks for posting that.


----------

