# Return latest Alpha / Numeric / Numeric Alpha Revision

1. ## Return latest Alpha / Numeric / Numeric Alpha Revision

Hello,

I'm trying to find a formula that will return the latest revision from a range A1:A7. This range could include values A, B, 0, 1, 1A, 1B, 2, etc. and in the order listed would be from oldest revision to the latest revision. Using the values provided, the formula would return "2". If my latest revision was "1A", then the formula would return "1A". The range A1:A7 is fixed so if there were more than 7 revisions, the 8th revision would overwrite the 1st revision.

Thank you for any help in advance.  Register To Reply

2. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision

If you only have 7 cells and they will all be filled, why not just =A7?  Register To Reply

3. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision

Try this

=INDEX(A1:A7,MAX(LOOKUP(99,A1:A7, ROW(A1:A7)), LOOKUP("ZZZ", A1:A7, ROW(A1:A7))))  Register To Reply

4. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision

The cells will not always be full and A7 would not necessarily be the latest revision. Any of the cells could contain the latest revision.  Register To Reply

5. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision

Please see the attached image file (couldn't attached excel for some reason) for examples and expected results for each situation. Revision Example.jpg  Register To Reply

6. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision Originally Posted by ChemistB Try this

=INDEX(A1:A7,MAX(LOOKUP(99,A1:A7, ROW(A1:A7)), LOOKUP("ZZZ", A1:A7, ROW(A1:A7))))
This formula partially worked. It returned a "Not Available" error if all values were all numeric and this is a possibility. If the revisions get larger and move back to the top of the range (cell A1) then the formula will only return the largest value in cell A7. I posted a picture of some possibilities for better clarity on the problem.  Register To Reply

7. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision

Try this

=INDEX(A3:A10,MAX(IFERROR(LOOKUP(99,A3:A10, ROW(A3:A10)-2), LOOKUP("ZZZ",A3:A10, ROW(A3:A10)-2)),IFERROR(LOOKUP("ZZZ", A3:A10, ROW(A3:A10)-2),0)))  Register To Reply

8. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision

This formula works great for the first 4 examples but does not return the correct result on Example 5. It is returning Revision "3" rather than Revision "4" (referring to the image of examples that I posted earlier).  Register To Reply

9. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision

My mistake, I thought you were looking for the last entry in the range. My formula will not do what you want. How do you define your hierarchy?

So it looks like 1A is greater than a numerical value. Is 1b greater than 2a? Is A greater than a numerical value?  Register To Reply

10. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision

Okay, If I am understanding correctly, try this one

=IFERROR(INDEX(A3:A9,LOOKUP(2,1/SEARCH(MAX(A3:A9),A3:A9), ROW(A3:A9)-2)), LOOKUP("ZZZ", A3:A9))  Register To Reply

11. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision Originally Posted by ChemistB Okay, If I am understanding correctly, try this one

=IFERROR(INDEX(A3:A9,LOOKUP(2,1/SEARCH(MAX(A3:A9),A3:A9), ROW(A3:A9)-2)), LOOKUP("ZZZ", A3:A9))

YES! This works, Thank you!  Register To Reply

12. ## Re: Return latest Alpha / Numeric / Numeric Alpha Revision  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 