referencing a cell

[Ahmadnoori]

My summary sheet makes a table for the profits, costs, etc. of my company for every year. So there are sheets for 2010-2022 all of which have the following format:

2022: 

        A                    B    

1: January

2: Profit                10, 000

3: Costs                  5, 000

4: February

5: Profit                20, 000

6: Cost                   8,000

.

.

I know that if I wanted to refer to the cell B5 (profit for February) in my summary sheet, I could do ='2022'!$B5 but I don't want to do this because in the future, profit may be in the 3rd row or 4th row etc. 

Is there any way to reference the cell so that it still gives me the profits for February regardless of what row it moves to ? 

I tried to use a match function with "Profit" as the lookup_value but I had trouble figuring out how to specify the profit for a specific month. I came up with this:

INDIRECT(MATCH("Profit", INDIRECT(CONCATENATE("A", MATCH("February", A1:100, 0))):A100, 0))

The idea is that I am looking for "Profit" but only within a specific interval which is given by INDIRECT(CONCATENATE("A", MATCH("February", A1:100, 0)) which spits out A4. So the function simplifies to INDIRECT(CONCATENATE("B",MATCH("Profit", A4:A100, 0))) which works and gives me 20,000 but only when I use it in the 2022 sheet.

The issue with this is that I don't know how to use this in my summary sheet. I tired to do ='2022'!CONCATENATE("B", MATCH("February", '2022'!A1:100, 0)) but this doesn't work.

Any help would be greatly appreciated!

[#2]

@Ahmadnoori 

=INDEX('2022'!B:B, MATCH("February", '2022'!A:A, 0)+1)

[#3]

@Hans Vogelaar 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! 

[#4]

@Ahmadnoori 

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))

[#5]

@Hans Vogelaar 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 :)

[#6]

@Ahmadnoori 

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.

[#7]

You are an absolute legend! Thank you so much :)