Formula to get Last Cell with Values

[smartbuyer]

Hi All,

How do I convert this formula to find the value of the last cell with value in a range.

Please Login or Register  to view this content.

See attached for a examples and results I'm after. Book1.xlsx

Thanks!

[vlady]

maybe this one

=INDEX(G2:G11,MATCH(9.99999999999999E+307,H2:H11))

why not use iferror instead of isna
also array

=iferror(INDEX(A2:A11,MATCH(1,IF(B2:B11<>0,IF(B2:B11<>"",1)),0)),"")

Edit: Or non array

=INDEX(A2:A11,MATCH(TRUE,INDEX(ISNUMBER(B2:B11),0),0))

[benishiryo]

hi there. try:
=LOOKUP(2,1/(B2:B11<>0),A2:A11)

like what vlady mentioned; to counter errors in Excel 2007 & above, you could actually use IFERROR.
=IFERROR(LOOKUP(2,1/(B2:B11<>0),A2:A11),"")

if you need it to work in lower versions, then continue to use ISNA

[smartbuyer]

Thank you!

maybe this one

=index(g2:g11,match(9.99999999999999e+307,h2:h11))

why not use iferror instead of isna
also array

=iferror(index(a2:a11,match(1,if(b2:b11<>0,if(b2:b11<>"",1)),0)),"")

edit: Or non array

=index(a2:a11,match(true,index(isnumber(b2:b11),0),0))

[arlu1201]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.