CODEWITHSUNDEEP
excelexcel-formulaformula
EfhK
EfhK

Reputation: 123

Excel Index Match Formula last change

I've got a column U and a column L.
What I need to get is the value from column L when searched in column U.

    Column L          Column U  
      516               11  
      123               11   
      74                5  
      46                11
      748               21  
      156               11  
      189               21
      187               21

For example:
I want to search 21 in column U but I need to find the first value from the last change
(the number stays the same further in the column).
So if I want the value belonging to 21 I need to get 189.

I tried it with (thanks to Scott Craner):
=INDEX($L:$L,AGGREGATE(14,6,ROW($U$10:$U$500)/($U$10:$U$500=D2),1))
But this gets me the last 21 value so 187 as answer.

Does anybody know how to solve this?

Upvotes: 1

Views: 112

Answers (2)

Jacob Edmond
Jacob Edmond

Reputation: 808

If I understand you correctly, I believe this accomplishes the task:

=INDEX($L:$L,MATCH(INDEX($L:$L,AGGREGATE(14,6,ROW($U$9:INDIRECT("$U$"&MATCH(INDEX($L:$L,AGGREGATE(14,6,ROW($U$10:$U$500)/($U$10:$U$500=D2),1)),$L:$L,0)))/($U$9:INDIRECT("$U$"&MATCH(INDEX($L:$L,AGGREGATE(14,6,ROW($U$10:$U$500)/($U$10:$U$500=D2),1)),$L:$L,0))<>D2),1)),$L:$L,0)+1)

It builds on Scott's concept, and finds the 187, then limits the search arrays to the row where it finds 187, and finds the last row that does not have the number you are searching for (21). Then it indexes the first row after that, which will be the first match in the last series.

enter image description here

Upvotes: 2

user4039065
user4039065

Reputation:

You need to find the last row in column U with the value from D2 where the row immediately above it is not the same value.

=INDEX(L:L, AGGREGATE(14,6,ROW(U$10:U$500)/((U$10:U$500=D2)*(U$9:U$499<>D2)),1))

enter image description here

Now change U15 to be another 21. The result becomes,

enter image description here

For a dynamic number of values in columns L and U (starting in row 10), use,

=INDEX(L:L, AGGREGATE(14,6,ROW(U$10:INDEX(U:U, MATCH(1E+99, U:U)))/((U$10:INDEX(U:U, MATCH(1E+99, U:U))=D2)*(U$9:INDEX(U:U, MATCH(1E+99, U:U)-1)<>D2)),1))

Upvotes: 2

Related Questions