Cannot get Offset and Match formula to work

[endoskeleton]

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

Please Login or Register  to view this content.

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]

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]

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

[edvwvw]

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

[endoskeleton]

@ martindwilson

Fantastic, that worked perfectly.

Cheers

Jon