• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 58
  • Last Modified:

RETURN LAST VALUE

Hi,
I would like a formula to return the last non zero non blank value in column AM on SHEET2
Many thanks
Ian
0
racepro
Asked:
racepro
  • 4
  • 3
1 Solution
 
Rgonzo1971Commented:
HI,

pls try ((as an array formula Ctrl-Shift-Enter)
=INDEX(Sheet2!AM:AM,MAX(IF(Sheet2!AM:AM<>0,ROW(Sheet2!AM:AM),"")))

Open in new window

Regards
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...

=INDEX(AM:AM,MATCH(9^9,AM:AM))

Open in new window

0
 
raceproretiredAuthor Commented:
Please note it is for sheet2
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
raceproretiredAuthor Commented:
Neeraj I've added SHEET2! to the formula
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Or you may replace the lookup value in the Match function with 1E+207 if any of the numbers in column AM may equal to 387420489 (9^9).

=INDEX(AM:AM,MATCH(1E+207,AM:AM))

Open in new window

1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In that case, try this... :)

=INDEX(Sheet2!AM:AM,MATCH(1E+207,Sheet2!AM:AM))
1
 
raceproretiredAuthor Commented:
Thanks Neeraj it works. I prefer this formula to a curly bracket one
Ian
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Ian! Glad it worked as desired. :)
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now