The values in A1:F1 must be sorted for this to work. If the LOOKUP function can't find the lookup_value (i.e. "zzzzzz..."), the function matches the largest value in lookup_vector (A1:F1 in this case) that is less than or equal to lookup_value.
Hence it finds the last item within the array.
A similar formula can be used for finding the last number in a numerical array, i.e.:
=LOOKUP(10E10,A1:F1)
where 10E10 and REPT("z",255) are just very large values.
Hope this helps.
Pete
so this would always be the rightmost cell with any value in even if there were cells further left with beginning with letters further up the alphabet. does largest value in lookup vector mean highest cellOriginally Posted by Pete_UK
i just cant quite grasp the logic behind this
The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
<--- If a post helps hit the star
The data has to be sorted, so your comments don't apply.
Suppose you have:
A, D, F, M
in those cells. Then the formula will return M, as it is the highest value less than "zzzzzz...".
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks