Cannot get Offset and Match formula to work

**endoskeleton** · 06-03-2011, 09:33 AM

Hi

I am having trouble getting the MATCH section of my formula to work.

The offset section of things is working fine with the following staring point

=(F5)*OFFSET('PUB 400128 - Table 1'!BR12,-2,-15,1,1)

However I do not want the starting point of the offset to be cell BR12 (as it is in the formula above). I want it to be the last cell with data in row 12, so I have been trying to use MATCH to find that position as the starting point, but no matter what I do I cannot find the correct solution.

Help please !

Many tanks

Jon

**ChemistB** · 06-03-2011, 09:52 AM

Assuming the values are numeric

=F5 * OFFSET(INDIRECT(ADDRESS(MATCH(LOOKUP(9^300,$BR$1:$BR$500),$BR$1:$BR$500,0),1,4)),-1,0)

**martindwilson** · 06-03-2011, 09:56 AM

or maybe just
=f5*INDEX(10:10,MATCH(9.999999999E+307,12:12,1)-15)

**edvwvw** · 06-03-2011, 10:06 AM

HI

If you can use a helper cell then you can look for the position of the first blank cell in row 12

in the helper: =NOT(ISBLANK(A12))*1 drag across the first blank column will give a zero
then use =MATCH(MIN(A11:Z11),A11:Z11,0) to find the first zero in your "ISBLANK" array

then offset as required.

edvwvw