Row reference in a formula when the row can move about.

[Eamonn100]

Hi,

I'm using =VLOOKUP("Fiscal Period ",$A:$AB,COUNTA(1:1),0) to find the end of a row. The problem is sometimes Fiscal Period isn't in row 1:1. Is there away for me to reference what ever row reference Fiscal Period is in. e.g. If Fiscal Period was in row 5:5 my formula would be =VLOOKUP("Fiscal Period ",$A:$AB,COUNTA(5:5),0)

Below is a formula for returning the row reference but it's not working properly when I incert it into the main formula, e.g. =VLOOKUP("Fiscal Period ",$A:$AB,COUNTA(MATCH("Fiscal Period ",A:A,0)&":"&MATCH("Fiscal Period ",A:A,0)),0)

=MATCH("Fiscal Period ",A:A,0)&":"&MATCH("Fiscal Period ",A:A,0) This on it's own returns It returns 1:1.

[TABLE="width: 1921"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]l[/TD]
[TD]m[/TD]
[TD]n[/TD]
[TD]o[/TD]
[TD]p[/TD]
[TD]q[/TD]
[TD]r[/TD]
[TD]s[/TD]
[TD]t[/TD]
[TD]u[/TD]
[TD]v[/TD]
[TD]w[/TD]
[TD]x[/TD]
[TD]y[/TD]
[TD]z[/TD]
[TD]aa[/TD]
[TD]ab[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Fiscal Period[/TD]
[TD="align: right"]Dec-05[/TD]
[TD="align: right"]Dec-06[/TD]
[TD="align: right"]Dec-07[/TD]
[TD="align: right"]Dec-08[/TD]
[TD="align: right"]Dec-09[/TD]
[TD="align: right"]Sep-10[/TD]
[TD="align: right"]Sep-11[/TD]
[TD="align: right"]Sep-12[/TD]
[TD="align: right"]Sep-13[/TD]
[TD="align: right"]Sep-14[/TD]
[TD="align: right"]Dec-15[/TD]
[TD]TTM/current[/TD]
[TD][/TD]
[TD="align: right"]Mar-13[/TD]
[TD="align: right"]Jun-13[/TD]
[TD="align: right"]Sep-13[/TD]
[TD="align: right"]Dec-13[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]Jun-14[/TD]
[TD="align: right"]Sep-14[/TD]
[TD="align: right"]Mar-15[/TD]
[TD="align: right"]Jun-15[/TD]
[TD="align: right"]Sep-15[/TD]
[TD="align: right"]Dec-15[/TD]
[TD="align: right"]Mar-16[/TD]
[TD="align: right"]Jun-16[/TD]
[TD="align: right"]Sep-16[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eamonn100]

Sorry, my working out sheet was a mess and was "messing" up my results. It's working fine now.

 

[Eamonn100]

I've found out that my formula works fine when on the same sheet as the information but when I try to use it from another sheet (as I have a copy/paste sheet for the information and a working out sheet for formulas I use to extract the info I need) it doesn't work properly.

Can anyone see why this would work when on the same sheet but not when it's on a different sheet to the info?

Prehapes a simpler way to find the row reference than my formula. =MATCH("Fiscal Period ",A:A,0)&":"&MATCH("Fiscal Period ",A:A,0)

 

[Eamonn100]

Hi,

I'm using =VLOOKUP("Fiscal Period ",$A:$AB,COUNTA(1:1),0) to find the end of a row. The problem is sometimes Fiscal Period isn't in row 1:1. Is there away for me to reference what ever row reference Fiscal Period is in. e.g. If Fiscal Period was in row 5:5 my formula would be =VLOOKUP("Fiscal Period ",$A:$AB,COUNTA(5:5),0)

Below is a formula for returning the row reference but it's not working properly when I incert it into the main formula, e.g. =VLOOKUP("Fiscal Period ",$A:$AB,COUNTA(MATCH("Fiscal Period ",A:A,0)&":"&MATCH("Fiscal Period ",A:A,0)),0)

=MATCH("Fiscal Period ",A:A,0)&":"&MATCH("Fiscal Period ",A:A,0) This on it's own returns It returns 1:1.

[TABLE="width: 1921"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]l[/TD]
[TD]m[/TD]
[TD]n[/TD]
[TD]o[/TD]
[TD]p[/TD]
[TD]q[/TD]
[TD]r[/TD]
[TD]s[/TD]
[TD]t[/TD]
[TD]u[/TD]
[TD]v[/TD]
[TD]w[/TD]
[TD]x[/TD]
[TD]y[/TD]
[TD]z[/TD]
[TD]aa[/TD]
[TD]ab[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Fiscal Period[/TD]
[TD="align: right"]Dec-05[/TD]
[TD="align: right"]Dec-06[/TD]
[TD="align: right"]Dec-07[/TD]
[TD="align: right"]Dec-08[/TD]
[TD="align: right"]Dec-09[/TD]
[TD="align: right"]Sep-10[/TD]
[TD="align: right"]Sep-11[/TD]
[TD="align: right"]Sep-12[/TD]
[TD="align: right"]Sep-13[/TD]
[TD="align: right"]Sep-14[/TD]
[TD="align: right"]Dec-15[/TD]
[TD]TTM/current[/TD]
[TD][/TD]
[TD="align: right"]Mar-13[/TD]
[TD="align: right"]Jun-13[/TD]
[TD="align: right"]Sep-13[/TD]
[TD="align: right"]Dec-13[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]Jun-14[/TD]
[TD="align: right"]Sep-14[/TD]
[TD="align: right"]Mar-15[/TD]
[TD="align: right"]Jun-15[/TD]
[TD="align: right"]Sep-15[/TD]
[TD="align: right"]Dec-15[/TD]
[TD="align: right"]Mar-16[/TD]
[TD="align: right"]Jun-16[/TD]
[TD="align: right"]Sep-16[/TD]
[/TR]
</tbody>[/TABLE]

Edit
The first formula should read. =VLOOKUP("Fiscal Period ",$A:$AB,COUNTA(INDIRECT(MATCH("Fiscal Period ",A:A,0)&":"&MATCH("Fiscal Period ",A:A,0))),0)