IF referance multiple criteria Formula Help

[Yogi_Bear_79]

I would like to set a cell todisplay the value from another cell on a
differant sheet, in the same workbook. I need it to look in a column range
from F2:F14. It should select the data from the last cell used that does'nt
contain a zero. I know how to do a basic IF statement, but not sure how the
syntax would work on this one. I would prefer to keep this one a formula
versus a VBA macro

 

[Ron Rosenfeld]

I would like to set a cell todisplay the value from another cell on a
differant sheet, in the same workbook. I need it to look in a column range
from F2:F14. It should select the data from the last cell used that does'nt
contain a zero. I know how to do a basic IF statement, but not sure how the
syntax would work on this one. I would prefer to keep this one a formula
versus a VBA macro

With your range named "rg", this *array-formula* will work:

=INDEX(rg,MAX((rg>0)*ROW(rg))-ROW(rg)+1)

To enter an *array-formula*, after typing or pasting it into the formula bar,
hold down <ctrl><shift> while hitting <enter>. XL will place braces {...}
around the formula.

Note that it is not necessary to NAME rg. I wrote it that way so the formula
can be more easily generalized to other ranges.


--ron

 

[Tom Ogilvy]

You could simplify this

=INDEX(Sheet1!F1:F14,MAX((Sheet1!F2:F14>0)*ROW(F2:F14)))

Entered with Ctrl+Shift+Enter

by starting the first argument of index in row 1, you don't need to subtract
anything. It only uses F1 as an anchor - it would not return anything from
F1 unless there are no values in F2:F14 greater than 0

 

[Yogi_Bear_79]

Tom,

Thanks for your time, your suggestions works great. I have one more
twist to add. In some instances the data in cells F2:F14 may currently be
blank. When that happens the formula returns the header row cells name
versus a zero. I tried to incorporate an IF statement but it always returns
false in my tests. Probably becuase I am testing for the header cells value
which is text.

 

[Tom Ogilvy]

If F2:F14 will be numbers (or blank)

=IF(SUM(ISNUMBER(Sheet1!F2:F14)*(Sheet1!F2:F14<>0))=0,"",INDEX(Sheet1!F1:F14
,MAX((Sheet1!F2:F14>0)*ROW(F2:F14))))

Entered with Ctrl+Shift+Enter