MATCH a cell to a Range of a periodically updated list of records

In a worksheet 'Sheet1', with current race data displayed, a column of values 20 rows long, has a formula in each cell:

=IF(MATCH(A1,Sheet2!A$1:Sheet2!A$20,0),"LAY".

Next column cell:

=IF(MATCH(A2,Sheet2!A$1:Sheet2!A$20,0),"LAY".....and so on down to row 20

However the table of values on Sheet2, updates after each race and another table of 20 rows is added. I need the formula in each cell of the next race to be:"

=IF(MATCH(A1,Sheet2!A$21:Sheet2!A$40,0),"LAY".

Next column cell:

"=IF(MATCH(A2,Sheet2!A$21:Sheet2!A$40,0),"LAY"...and so on down to row 40

In other words, the active range of cells on Sheet1, is always matching with the next updated range of cells on Sheet2. I would prefer a non vba solution as the active worksheet has a lot of other vba happening that I've finally got stable and don't want to disturb it...LOL

Re: MATCH a cell to a Range of a periodically updated list of records

Quote from pangolin;739828

put this formula in 1st row and copy down

=IF(MATCH(A1,INDIRECT(ADDRESS(((ROUNDUP(ROW()/20,0)-1)*20)+1,1,,,"Sheet2")& ":"&ADDRESS(ROUNDUP(ROW()/20,0)*20,1)),0),"LAY","")

Hi pangolin, and thankyou very much for your response. I can't get the function to update if I paste another 20 rows of data after the last 20 in sheet2. I've made up a simple worksheet for you to look at. I'm sure it's a minor issue.

Just imagine that a vba formula pastes a fresh set of 20 values to sheet2 each time a new race appears on the sheet1, and then 30 secs later the continually updating values on sheet1 freeze, a comparison is made with the last 20 values in the sheet2 column and the instruction "Lay" is fired if there's a match.

Thanks