Issue with formula

[steB2]

Hi all,

I have a formula I am trying to use and have worked it out in the main, but am struggling with the last bit. Any help greatly appreciated.

=G3#*(INDEX(AB4:AB5,MATCH(Z3,AA4:AA5,0)))

This is the formula and is working as should with the exception of Z3 part. I am essentially trying to use the array to automatically drop the formula into the cells and it has worked but the referencing of Z3 should change and the formula goes down to Z4, Z5 etc. I change the Z3 to Z3# but get reference error although it works before just not referencing right cell.

I also tried the following to get same result and again it works but moment I change the Z3 to Z3# I get reference error.
=G3#*(VLOOKUP(Z3,AA4:AB5,2,0))


Help what am I doing wrong?

 

[steB2]

 

[Fluff]

Does Z3 contain a formula that spills down?

 

[steB2]

Does Z3 contain a formula that spills down?

Hi Fluff, no Z3 just contained numbers as I was just testing. Now it has just clicked what I was doing wrong. I have added in the formula into Z3 I was going to use and it has now worked.

Thank you for taking to time to help!!!!!!!!!

 

[Fluff]

Glad you sorted it & thanks for the feedback.