Index Match - bring back 2nd match

[psrs0810]

I am trying setup an index match but I have 2 of the same value. How can I setup my index match to bring back the second match?
thanks

 

[tyija1995]

Hey, you can offset the table to start at 1 row underneath the first match.

Say you are looking up for the ID value of 15 in a table governed in A1:C10, then this will return the 2nd case of 15 in column A (if there is a 2nd case):

=INDEX(OFFSET(A1,MATCH(15,A:A,FALSE),0):C10,MATCH(15,OFFSET(A1,MATCH(15,A:A,FALSE),0):A10,FALSE),2)

Will return the value in column B that is from the 2nd value of 15 in column A.

 

[Eric W]

If you know there are exactly 2 matches, you can use the formula in E2 that returns the last match. If there may be more matches, you can use the array formula in F2 which will let you pick which match you want.

	A	B	C	D	E	F
List	Value	Key	Last Value	2nd value
a	b
b
c
d
a
b
e
f
g
b

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=LOOKUP(2,1/(A2:A11=D2),B2:B11)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=INDEX(B2:B11,SMALL(IF(A2:A11=D2,ROW(A2:A11)-ROW(A2)+1),2))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[psrs0810]

tyija1995 - I tried using the code above, and I want not able to return a value.

 

[jbenfleming]

Maybe this will help:

https://exceljet.net/formula/get-nth-match-with-index-match

 

[AhoyNC]

Using the data example from Eric W here is another option if you are using Excel ver. 2010 or later.

Excel Workbook
	A	B	C	D	E	F
1	List	Value		Key	Last Value	2nd value
2	a	1		b	10	6
3	b	2
4	c	3
5	d	4
6	a	5
7	b	6
8	e	7
9	f	8
10	g	9
11	b	10
Sheet