Forum Discussion
Show value of last cell to the left that has a value
Later versions can have lower values in their cell than previous versions. I dont need the number of the version, I need the value of that. Ersion of the report. The score if you want.
JMB17 yes, still shows the MAX,not the value of the latest version
Hi guydegrieck
Your formula worked for something that i'm working in
But i just want to know what do number 2 and number 1 divided by range of cells mean?
why number 2?
why number 1 divided by range of cells?
this is something that i didn´t get
LOOKUP(2,1/($E3:$AI3<>0),$E3:$AI3),0)
It is a traditional method for locating the final occurrence of a value in a list that relies upon some 'tips and tricks'. Firstly
$E3:$AI3<>0
discriminates between occupied and unoccupied cells, giving TRUE and FALSE respectively.
Within an arithmetic expression TRUE and FALSE are treated as 1 and 0 so the division returns a row of values 1 or #DIV/0! errors.
LOOKUP ignores errors and performs an approximate match (the largest value less than or equal to the search value in a sorted list). By giving it 2 to search for, it will return the content of the final cell for which the formula result evaluated to 1.
Most things are possible with traditional Excel, but they tend to rely upon an encyclopaedic knowledge of such 'tips and tricks', as well as finely tuned 'muscle memory' for filling formulas down etc. by comparison, Excel 365 makes more of a 'programming' mindset available, with resulting formulas that do not require as many manual adjustments to adjust to new problem data.
If this case, one might use
= XLOOKUP(TRUE, versions<>"", versions, "Not available", ,-1)
where the final parameter instructs the function to return the right-most match.
That is fine if one has only a single versioned document but, in reality, where there is one, there are likely to be more. In such a case, one might turn to
= BYROW(documents, LAMBDA(version, XLOOKUP(TRUE, version<>"", version, "Not available", ,-1) ))to return the final version of every document as a dynamic array.
- Did you enter it exactly as shown? Note there is a +1 after MAX.
=LOOKUP(MAX(B2:F2)+1,B2:F2)
If it's not working, is it possible you can upload an example. Since it has always worked for me to find the last number in a range, I am curious why it is not working this time.Don't forget that LOOKUP requires the search range to be sorted ascending (though errors are ignored).
SergeiBaklan although I don't have a clue on how this formula does the trick, it seems to work. So guess I will just be happy with the result. Thank you very much. And also for others that reacted.
When I look at some of these formula's , my question was probably a bit simplistic for you, but it helps me a lot. Thanks again.
Guy
