|First: Your example formula contains semi-colons. Excel uses commas, so that is what I am going to use.|
As for your question, you should be able to accomplish your goal by using the ROW() and OFFSET() functions.
The ROW() function will return the number of the Row in which the function resides. e.g used anywhere in Row 1, =ROW() will return 1. Used anywhere in Row 473, =ROW() will return 473. That number can be used as the cols argument for the OFFSET function.
For example, if your first MATCH function is in Row 1, then these 2 formulas are equivalent:
By using 0 as the rows argument for OFFSET, there will never be a Row offset. By using ROW()-1 as the cols argument, there will be no Column offset because ROW() will return 1 and 1-1 = 0. Again, that is based on the fact that the formula is in Row 1. (More on that later)
When you drag the formula down into Row 2, the Column offset will be 1 because ROW() will return 2 and 2-1 = 1. Therefore the MATCH function will now look at Column K, which is 1 Column offset from J.
Here is the "More on that later" part: If your MATCH function is not in Row 1, you will need to adjust the cols argument so that ROW()-? will equal 0 based on which Row the first MATCH function is in.
Please note that the formula itself will not change when you drag it down. It will look the same in all cells, but it will referencing a different column because the value returned by the ROW() function will increment each time you drag it down, incrementing the Column offset value.
If you want to see the Column reference change, choose any formula in your range and use the Evaluate Formula feature to single-step through the formula. Once the OFFSET portion is evaluated, the new Column reference will be shown.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03