why offset formula displays '0'

[arifanmol]

plz read the content in attached worksheet and revert to me for a charming smile !!!

 

[Luke M]

Address and MATCH function tell the formula to look in G9. Then you told it to offset 2 rows in the first OFFSET function, landing you on G11. Then you told the outer OFFSET to go down 1 row and over 2 columns, landing you in I12, which is a blank cell. Thus, the value is 0.

Overall, this is a very clunky and volatile function. I'm not sure what the overall goal was. If it's to find the value in col G that's 2 below the Subtotal line:

=INDEX(G:G, MATCH("Subtotal", F:F, 0)+2)

 

[joeu2004]

I'm not sure what the overall goal was

Refer to https://chandoo.org/forum/threads/displaying-value-in-formula-output.51052
and to my response, posting #5.

 

[arifanmol]

first find 'subtotal from whole worksheet, then go to right, there sum range : 1st cell to cell just above =INDEX(G:G, MATCH("Subtotal", F:F, 0)
then come back automatically to cell under it then use this formula =INDEX(G:G, MATCH("Subtotal", F:F, 0)+2)