# 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

put this formula in 1st row and copy down

• 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

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

## Files

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

after pasting fresh set of data you have to come to this sheet and copy paste this formula to the next 20 rows and after every 20 rows the range references in the formula change as you want....

this paste of formula is not VBA driven...you will have to do manually

and it is as per your first post where you said that you "prefer a non vba solution"

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!