Forum Discussion
referencing a cell
=INDEX('2022'!B:B, MATCH("February", '2022'!A:A, 0)+1)
HansVogelaar first of all, thank you for your reply ! This works but the issue with it is that it relies on Profits being the first row after February which may not always be the case in the future. Is there anyway to adjust the code to take this into consideration ? I know I could just change the +1 accordingly but I want something more efficient. Thanks again!
Here is a formula that will work in Excel in Microsoft 365, Office 2021 and online (in the browser):
=LET(m, "March", t, "Profit", p, MATCH(m, '2022'!$A:$A, 0), r, INDEX('2022'!$A:$A, p+1):INDEX('2022'!$A:$A, p+1000), q, MATCH(t, r, 0), INDEX('2022'!$B:$B, p+q))
Instead of the literal strings "March" and "Profit", you can use cell references, for example if A2 contains March and B2 contains Profit:
=LET(m, A2, t, "P2, p, MATCH(m, '2022'!$A:$A, 0), r, INDEX('2022'!$A:$A, p+1):INDEX('2022'!$A:$A, p+1000), q, MATCH(t, r, 0), INDEX('2022'!$B:$B, p+q))
HansVogelaar this is perfect! Thank you so much !! It works but I’m not quite sure why. Can you please explain the code / your thought process ? I would appreciate it very much ! Thanks again 🙂
m is the month ("March"), t is the type ("Profit").
p, defined as MATCH(m, '2022'!$A:$A, 0), finds the row number of the specified month in column A.
r, defined as INDEX('2022'!$A:$A, p+1):INDEX('2022'!$A:$A, p+1000), is the range in column A from the row below the row with the month to 1000 rows below (the 1000 is an arbitrary large number).
q, defined as MATCH(t, r, 0), is the index within the range r of the first cell that contains the specified type.
p+q is the row number of that cell. We use this to return the value in that row in column B.