oops! I didn't pay attention to sort limitation in your query. Here's the equivalent of Lookup() for unsorted data:
=Offset(J3, Match(Large(J3:J91, 4), J3:J91, 0) - 1, -8) The Offset() function returns a value from the cell that is X rows and Y columns away from the specified cell. The syntax is: Offset(start_position, x, y) where, start_position = the cell co-ordinates of the cell to reference while offsetting. x = number of rows to offset - 0 for current row, positive value for below and negative value for above. y = number of columns to offset - 0 for current column, positive value for right and negative value for left. The Match() function returns the relative position of the specified item in an array. The syntax is: Match(value, cellRange, matchType) where, value = the value to be matched. cellRange = the range of cells to look for. matchType = the type of match - 0 means exact match, 1 means greater than and -1 means lesser than. Hope this solves your problem! -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! -----Original Message----- From: accessindia-boun...@accessindia.org.in [mailto:accessindia-boun...@accessindia.org.in] On Behalf Of Aravind Kumar Sent: Thursday, August 30, 2012 8:20 PM To: accessindia@accessindia.org.in Subject: Re: [AI] new doubt in excel dear Zujar, thank you. your large function coupled with lookup worked upto some rank like 3. but after some ranks, its displaying wrong branch names some times. then i checked with excel help of lookup. there, one warning is given as if numbers are not ordered correctly whether ascending or descending, lookup may give unexpected result. so we have to analyse this function more deeply. Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@accessindia.org.in with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit the list home page at http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@accessindia.org.in with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit the list home page at http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
