CODEWITHSUNDEEP
excelexcel-formula
Asif Chowdhury
Asif Chowdhury

Reputation: 65

Need to find the Second matching value from a range of rows in excel sheet

Need to find the Second matching value from a range of rows. Sample is attached. Table on the right side should be the output. Such as when I select 41110, if there are two 41110, value should be the second one on the list.

Data and Expected result...

enter image description here

Upvotes: 1

Views: 631

Answers (2)

user4039065
user4039065

Reputation:

Use the AGGREGATE¹ function with sub-function 15 (SMALL), option 6 (discard errors) with a k of 2 (second matching occurance). Force any non-matches into an error state (e.g. #DIV/0!).

    aggregate_second

¹ The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.

Upvotes: 5

Harun24hr
Harun24hr

Reputation: 37135

Use following formula. I make this formula on basis of given data. If your actual data is different then you have to customize the formula as your own.

=INDEX($B$2:$B$10,LARGE(IF($A$2:$A$10=D2,ROW($A$2:$A$10)-ROW($A$1)),1))

enter image description here

Note: The formula is an array formula. So, you must press Ctrl + Shift + Enter after typing formula.

Upvotes: 9

Related Questions