Share via

How to I find the last match in an unsorted array? Match and index only provide the first as afar as I can see?

Anonymous
2016-09-24T23:45:48+00:00

How to I find the last match in an unsorted array? Match and index only provide the first as afar as I can see?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-09-25T01:09:22+00:00

    Hi Training.Goddess ,

    How to I find the last match in an unsorted array? Match and index only provide the first as afar as I can see?

    Assume that the unsorted data array is in A2:B100, the search value is in C2, try the array formula:

    =INDEX($B$2:$B$100,MAX(($C$2=$A$2:$A$100)*MATCH(ROW($A$2:$A$100),ROW($A$2:$A$100))))

    Note that as this is an array formula it should be confirmed with Ctrl+Shift+Enter

    ===

    Regards,

    Norman

    0 comments
  2. Anonymous
    2016-09-25T07:10:00+00:00

    You may try this non-array formula (per Norman's data range):

    =LOOKUP(2,1/($A$2:$A$100=C2),$B$2:$B$100)

    This will match the last occurrence of cell C2 value in column A and return the corresponding value from column B.

    Regards,

    Amit Tandon

    0 comments