1

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
David Austin
  • 37
  • 1
  • 9

1 Answers1

1

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))
Bryan Davies
  • 430
  • 1
  • 3
  • 13
  • Hi @BryanDavies. The table will be increasing all the time so I am looking for the formula to look to the bottom of the column automatically rather than a hard coded number of rows below - is this possible? – David Austin Jul 01 '16 at 08:43
  • There is, I just cant remember it... But in saying that, just put in a mill or something. It wont matter to that formulla – Bryan Davies Jul 01 '16 at 09:11
  • edit: google says =min(row(data))-rows(data)-1 https://exceljet.net/formula/last-row-number-in-range – Bryan Davies Jul 01 '16 at 09:17
  • 1
    I managed to use INDIRECT("B"&(ROW()+1)&":B"&COUNTA(B:B)) in the end - thanks for your help! – David Austin Jul 01 '16 at 10:20