Lookup only the second occurence

[Pauljj]

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 ?

 

[T. Valko]

Try this...

Book1
*	A	B	C	D	E
2	Lisa	8	_	Tom	67
3	Joe	20	_	_	_
4	Joe	32	_	_	_
5	Lisa	35	_	_	_
6	Biff	51	_	_	_
7	Tom	58	_	_	_
8	Tom	67	_	_	_
9	Biff	82	_	_	_
Sheet1

This formula entered in E2:

=LOOKUP(2,1/(A2:A9=D2),B2:B9)

 

[Pauljj]

Thank you very much, works perfectly

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Biz]

Mate nice formula. How can I change it pick say 3rd or 4th occurence?

Biz

 

[T. Valko]

Are the names in random order or are they grouped together:

Random order:

Book1
*	A	B
2	Lisa	8
3	Joe	20
4	Joe	32
5	Lisa	35
6	Biff	51
7	Tom	58
8	Tom	67
9	Biff	82
10	Biff	23
11	Lisa	79
12	Joe	7
13	Tom	14
14	Lisa	89
15	Joe	25
Sheet2

Grouped together:

Book1
*	A	B
2	Biff	51
3	Biff	82
4	Biff	23
5	Joe	20
6	Joe	32
7	Joe	7
8	Joe	25
9	Lisa	8
10	Lisa	35
11	Lisa	79
12	Lisa	89
13	Tom	58
14	Tom	67
15	Tom	14
Sheet2

 

[Biz]

Random order pls

 

[Biz]

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.

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="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><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="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><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="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD> </TD><TD style="FONT-WEIGHT: bold">Occurrence</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Lisa</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">8</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Biff</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #008080; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">23</TD><TD> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #008080; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">82</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Joe</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">20</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Joe</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">32</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Lisa</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">35</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Biff</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">51</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Tom</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">58</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Tom</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">67</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Biff</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">82</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; COLOR: #ffffff; FONT-SIZE: 10pt">_</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Biff</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">23</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Lisa</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">79</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Joe</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Tom</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">14</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Lisa</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">89</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Joe</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">25</TD><TD> </TD><TD> </TD><TD> </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>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" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=21 width=64></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" 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

 

[Aladin Akyurek]

Mate nice formula.

Yes, it is, Due to Harlan Grove, who understood the source formula

LOOKUP(9.99999999999999E+307,Reference)

and did not hesitate to combine it with his favorite 1/X set up.

The formula yields the last numerical instance or a correlate of the last numerical instance.

See

http://www.mrexcel.com/forum/showthread.php?p=492425

for more.

How can I change it pick say 3rd or 4th occurence?

Biz

The question in its generic form does not belong to the class of "last value" problems. What you need is, taken up in its generic form...

G2, control+shift+enter, not just enter:

=INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=E2,ROW($A$2:$A$100)-ROW($A$2)+1),F2))

where E2 is a name and F2 3 (meaning Nth instance).

With some control:

=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")

Hope this helps.

 

[Biz]

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