Hi all,
I need your help to solve an issue. On one tab, I'm using a vlookup inside an hlookup to get the figures you see below:
HLOOKUP(E$3,Net_perf!$B$19:$AT$200,VLOOKUP($C12,Net_perf!$B$19:$AT$200,45,0),0)-1)
I'm only interested in quarter-end figures and all but one of the columns of Net_perf tab have just quarterly figures.
However, I need that one column as well. I can't change the format of this tab as it will get picked up for a chart (so I can't add month-end figures and then calculate the quarterly myself).
So my question is, using the same formula of a vlookup inside an hlookup to get the figure for a specific date, can I then tell Excel to multiply that figure and the 2 figures above? I would imagine Offset can do this, but from my experiments if I put my HL+VL formula inside the offset as the reference it doesn't work.
So, something like:
PRODUCT(OFFSET(see above,0,0,-3)) -> not sure if -3 for height works either.
Please let me know if there is anything unclear.
Thanks in advance for your help.
I need your help to solve an issue. On one tab, I'm using a vlookup inside an hlookup to get the figures you see below:
HLOOKUP(E$3,Net_perf!$B$19:$AT$200,VLOOKUP($C12,Net_perf!$B$19:$AT$200,45,0),0)-1)
I'm only interested in quarter-end figures and all but one of the columns of Net_perf tab have just quarterly figures.
However, I need that one column as well. I can't change the format of this tab as it will get picked up for a chart (so I can't add month-end figures and then calculate the quarterly myself).
So my question is, using the same formula of a vlookup inside an hlookup to get the figure for a specific date, can I then tell Excel to multiply that figure and the 2 figures above? I would imagine Offset can do this, but from my experiments if I put my HL+VL formula inside the offset as the reference it doesn't work.
So, something like:
PRODUCT(OFFSET(see above,0,0,-3)) -> not sure if -3 for height works either.
Please let me know if there is anything unclear.
Thanks in advance for your help.