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

Reply via email to