Excel Forum • My Online Training Hub

Get a formula where the first and last rows of a lookup range are the value of 2 calculated cells, not manually typed in.

Michael O'Flaherty

Wellington

New Member

Members

Forum Posts: 2

Member Since:
December 20, 2022

Offline

December 22, 2022 - 10:07 am

In Excel 2019 I have 2 sheets; Category_P&L_CM and Monthly_Sheet.
On sheet Category_P&L_CM column A has account names, column B has amounts for the current month and column C has the year to date amounts.
The list of account names and their data starts at row 10, and the number of the last row varies depending on the activity in the accounts.
The list of accounts is quite long and has a group for Northern Hills in it. This group starts where Northern Hills listed first in column A. Cell E3 has only the words Northern Hills.
Cell F4 has the formula =MATCH(E3,A:A,0) which gives me the row number where Northern Hills first appears, in October this was row 100.
Cell F7 has the formula {=MAX(ROW(A:C)*(A:C<>""))} which gives me the last row number where data appears, in October this was row 127.
On Sheet Monthly_Sheet cells P2 and P3 have the the values from F4 and F7 on sheet Category_P&L_CM.
The first row of my lookups is row 12.
Cell P12 has the following formula: =IF(B12<>"","",IFNA(VLOOKUP($G12,'Category_P&L_ CM'!$A$100:$C$127,2,FALSE),0)).
This formula applies to all cells below P12 in column P.
Next month I will have to change the 100 and 127 to a different row numbers.
How do I get the values of cells P2 and P3 to automatically define my lookup range, so I don't have to manually change the formulae each month?
Thanks Michael

MGB

Member

Members

Forum Posts: 32

Member Since:
December 13, 2021

Offline

December 22, 2022 - 3:36 pm

May you post sample file

Velouria

London or thereabouts

Member

Members

Trusted Members

Forum Posts: 574

Member Since:
November 1, 2018

Offline

December 22, 2022 - 7:20 pm

You could use:

=IF(B12<>"","",IFNA(VLOOKUP($G12,INDEX('Category_P&L_ CM'!$A:$A,$P$2):INDEX('Category_P&L_ CM'!$C:$C,$P$3),2,FALSE),0))

though I would probably define a name using those INDEX functions just to keep the formula more readable and easier to maintain.

Michael O'Flaherty

Wellington

New Member

Members

Forum Posts: 2

Member Since:
December 20, 2022

Offline

January 20, 2023 - 10:24 am

Hi MGB and Velouria

Thanks for your comments.
I was working on this until just before Christmas, and have just got back on my computer.
I find myself rusty trying to resume where I was at a month ago so have decided to abandon this project.
Nevertheless I am grateful for your interest

Kind regards
Michael