Index/Match Next Distinct Value

[temerson]

Hello -

I have two columns:

Column A | Column B

6022 WF102
6022 WF102
6023 WF104

I am using index/match to fill Column B using look up values in Column A. My goal is to grab the next distinct value in Column B. There are multiple vendors with the same product code and I need to create something that will grab the next distinct value if the cell above in Column B is the same. Thanks in advance!!!

 

[sijpie]

=INDEX(A2:B9,MATCH(6022,OFFSET(A2:A9,MATCH(6022,A2:A9,0),0),0)+MATCH(6022,A2:A9,0),2)

What that does is:
it finds the first occurance of 6022 (using the Match within the Offset() function)
Then it finds the first occurance of 6022 in the offset range (shifted by the result of the first match) using the Match at the start
To this it adds the row of the first occurance, and so now you have the second occurance.
Then it does the index and finds the value in the assigned column