Dyamic Match with :

[retrieverim]

Hi Excel Professionals,

I have this formula $C$950+MATCH(B1004,'1'!$B$46:$B$81,0) and I wish to change it to $C$950 + MATCH(B1004, "'1'!$B$" & $C$950+1 & ":" & $B$81,0) to make the formula dynamic. $C$950+MATCH(B1004,'1'!$B$46:$B$81,0) gives the formula that searches B46 to B81 for the phrase, "Cash to Liquidity Reserves". I wish to change the formula such that it searches for a range where the starting of the range is 1 row above the value of C950.

C950 contains the formula - =MATCH(B950,'1'!$B$1:$B$46,0) that gives the row of a cell that matches to the word "Income Tax". The value of C950 is row 45. B950 contains the word Income Tax. In rows B1:B46, there is a word Income Tax.

Thank you!

[bebo021999]

Are you trying to create a dynamic range in sheet 1 column B that starts from cell value first "Income tax" found and ends at B84?
INDEX('1'!B84:OFFSET('1'!$B$1,C2,),)
Then match the 1st "Cash to liquidity..." found in that range:
C6:

Please Login or Register  to view this content.

Copy to C10 & C14

[retrieverim]

Hi bebo, it works perfectly thank you. The Cell ends in B$802 but it doesn't matter since the code works.

By the way, for excel, are we able to use the ":" in a non chronological order? For example, usually I would see B1: B84 but in this case, it is B84:B57.

[bebo021999]

Nice to hear it works
If we type the range B84:B57, excel chance it to B57:B84 automatically
In this case we want B57 to be dynamic, and nail B84. So using B84:OFFSET, with OFFSET to DYNAMIC refer to B57