Find Column Number

Question

I am really embarrassed by asking this question it just seems like it would be easy to find somewhere, but I have searched Google and here to no avail.

My problem is that I need to find last column NUMBER used in a name range.

Each row on this worksheet has a name range, from AH through BU.  The row name range is: Range3, Range 4 and so forth.

Only looking at this point for an Excel formula.

Example:

AH3=10.5; AI3=15; AJ3=20; AK3=0 ... BU3=0

So starting I would think from BU3 look backwards at each cell until you find the cell that has a value greater then zero, and then give me that column number, so in the above example I should see the number 36.  I have tried Index-Match and just Match and all I get is NA or Value error message.

Answer 1

You may use either of below as an array formula (CTRL+SHIFT+ENTER):

=MATCH(9.99999999999999E+307,IF($AH$3:$BH$3>0,$AH$3:$BH$3))+COLUMN($AH$3)-1 

or

=MATCH(9.99999999999999E+307,IF(3:3>0,3:3))

The above return the last column Number in row 3 with a value > 0.

Regards,

Amit Tandon

Answer 2

Thanks so much for the solution to the problem.  BTW the first worked just fine, but I couldn't get the second to work at all.