CODEWITHSUNDEEP
excelexcel-formulaexcel-2010match
David Austin
David Austin

Reputation: 37

Excel find next match in table column

I am trying to find the next matching row in a table (not just a range) that matches the current cell's value. I have tried to pass in the column from the next row to the end of the column but cannot get it to work. All ideas are welcome! E.g.

Current idea that doesnt work: =MATCH([@Value],OFFSET([@Value],1,0):[Value])

Example data:
Row    Value    Next_Match
 1       1        #N/a
 2       2          4
 3       3          6
 4       2          5
 5       2        #N/a
 6       3        #N/a

Upvotes: 1

Views: 4372

Answers (1)

Bryan Davies
Bryan Davies

Reputation: 430

Try this

=OFFSET(B3,MATCH(B3,B4:B$100,0),-1)

assuming B100 is the last row and you want to match only afterwards... Otherwise use B2:B100 etc

Oh and data is in Columns A,B,C

Although the perfectionist in me rather's

=IF(ISNA(MATCH(B3,B4:B16,0)),"Last",OFFSET(B3,MATCH(B3,B4:B16,0),-1))

Upvotes: 1

Related Questions