CODEWITHSUNDEEP
google-sheets
JordanBelf
JordanBelf

Reputation: 3338

Using the result of the MATCH formula as a reference for a cell range?

I have a spreadsheet from which I need to get the row number of a cell wich matches a condition. For that I am using a formula like the following

=MATCH( ( if(LM!A:A,eomonth(LM!A:A,1)+1,) ),LM!A:A,2,0)

In that case the output is 29

What I would like to do now is incorporate that output inside a bigger function using that 29 as the range for my condition. For example in the following formula

=FILTER( LM!B29:B363 ; ROW(LM!B29:B363) =MAX( FILTER( ROW(LM!B29:B363) ; NOT(ISBLANK(LM!B29:B363)))))-VLOOKUP( ( if(LM!A:A,eomonth(LM!A:A,1)+1,) ),LM!A:G,2,TRUE)

I would like to replace B29 with B + the value from the MATCH output

How can I incorporate that into my formula?

Any tip will be much appreciated!

Thanks!

Upvotes: 3

Views: 3248

Answers (1)

AdamL
AdamL

Reputation: 24629

The short answer is to use INDIRECT. For example, if some_expression evaluates to 29, then INDIRECT("LM!B"&some_expression&":B363")will reference the range LM!B29:B363.

That being said, I think there must be a more straightforward way of doing what you require. Can you explain in words what you are trying to achieve?

Upvotes: 3

Related Questions