Lookup only the second occurence

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,047
I have a list of names, all of them appear in this list twice (column A) in column B is a reference number

How can I look up the second occurence of the name to return the reference number

All methods of looking up always find the first occurence ?
 
Hi Aladin,

Thanks for explanation.

I have used your formula below
=IF(COUNTIF($A$2:$A$100,E2)>=F2,INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=E2,ROW($A$2:$A$100)-ROW($A$2)+1),F2)),"Not Available")

It works like a charm.

Thanks again.

Biz

You are welcome. Thanks for providing feedback.
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Using Ramdom Order

I have vba that solve this problem.

Occurrence is in cell D2. Looking for second occurrence. Using Vba I get 82 which is correct answer but formula gives me 23.

Code:
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, 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

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 77px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 77px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD></TD><TD></TD><TD style="FONT-WEIGHT: bold">Occurrence</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Lisa</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">Biff</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #008080; TEXT-ALIGN: center">23</TD><TD></TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #008080; TEXT-ALIGN: center">82</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Joe</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">20</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Joe</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">32</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Lisa</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">35</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Biff</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">51</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Tom</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">58</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Tom</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">67</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Biff</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">82</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD style="FONT-SIZE: 10pt; COLOR: #ffffff; FONT-FAMILY: Verdana; TEXT-ALIGN: center">_</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Biff</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">23</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Lisa</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">79</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Joe</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">7</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Tom</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">14</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Lisa</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">89</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">Joe</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: center">25</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=LOOKUP(2,1/(A2:A15=D2),B2:B15)</TD></TR><TR><TD>G2</TD><TD>=Nth_Occurrence($A$2:$A$15,D2,$D$1,0,1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

The syntax is


<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=64 height=21></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>=Nth_Occurrence(range_look,find_it,occurrence,offset_row,offset_col)</TD></TR></TBODY></TABLE>


I am sure there must be a way using formulas to derive similar result where occurrence and be manipulated easily.

Biz
See this:

Lookup when there are multiple instances of the lookup value

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3
 
Upvote 0
=IF(COUNTIF($A$2:$A$100,E2)>=F2,INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=E2,ROW($A$2:$A$100)-ROW($A$2)+1),F2)),"Not Available")
Thought I'd add my thanks for this formula as well. Works great for what I need.
 
Upvote 0
Thought I'd add my thanks for this formula as well.

=IF(COUNTIF($A$2:$A$100,E2)>=F2,INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=E2,ROW($A$2:$A$100)-ROW($A$2)+1),F2)),"Not Available")
If you index the entire column it's less expensive.

=IF(COUNTIF($A$2:$A$100,E2)>=F2,INDEX($B:$B,SMALL(IF($A$2:$A$100=E2,ROW($A$2:$A$100)),F2)),"Not Available")
 
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,834
Members
452,947
Latest member
Gerry_F

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top