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.

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

aa

ab

1

Fiscal Period

Dec-05

Dec-06

Dec-07

Dec-08

Dec-09

Sep-10

Sep-11

Sep-12

Sep-13

Sep-14

Dec-15

TTM/current

Mar-13

Jun-13

Sep-13

Dec-13

Mar-14

Jun-14

Sep-14

Mar-15

Jun-15

Sep-15

Dec-15

Mar-16

Jun-16

Sep-16

<tbody>
</tbody>

 

[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.

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

aa

ab

1

Fiscal Period

Dec-05

Dec-06

Dec-07

Dec-08

Dec-09

Sep-10

Sep-11

Sep-12

Sep-13

Sep-14

Dec-15

TTM/current

Mar-13

Jun-13

Sep-13

Dec-13

Mar-14

Jun-14

Sep-14

Mar-15

Jun-15

Sep-15

Dec-15

Mar-16

Jun-16

Sep-16

<tbody>
</tbody>

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)