Prev: Track Changes Colour
Next: excel sorting
From: Andrew on 28 Mar 2010 17:48
Hi there,

I have three colums of data in a worksheet from A1 to C5, example;

Column A Column B Column C
ABC 5/2/10 02:00 Dog
DEF 5/2/10 02:15 Cat
ABC 5/2/10 03:47 Mouse
GHI 5/2/10 01:20 Bird
ABC 5/2/10 00:11 Horse

What I need to do in a seperate worksheet in A1 is to look up a value in
column A, find its lowest figure in column B and return the corresponding
value from column C. Then in A2 vlook up the same value in column A, find the
SECOND lowest figure in column B and return the corresponding value from
column C. Then in A3 the same but returning the THIRD lowest figure and on
and on. So using the example above with ABC as the look up value the results
would look like;

Horse
Dog
Mouse

Any help with this very appreciated. Thanks.
From: tompl on 28 Mar 2010 17:53
It would probably be easiest to just sort the data first on column A
ascending, then on column B ascending. Then you could copy it to the page
you want it on.

Tom

"Andrew" wrote:

> Hi there,
>
> I have three colums of data in a worksheet from A1 to C5, example;
>
> Column A Column B Column C
> ABC 5/2/10 02:00 Dog
> DEF 5/2/10 02:15 Cat
> ABC 5/2/10 03:47 Mouse
> GHI 5/2/10 01:20 Bird
> ABC 5/2/10 00:11 Horse
>
> What I need to do in a seperate worksheet in A1 is to look up a value in
> column A, find its lowest figure in column B and return the corresponding
> value from column C. Then in A2 vlook up the same value in column A, find the
> SECOND lowest figure in column B and return the corresponding value from
> column C. Then in A3 the same but returning the THIRD lowest figure and on
> and on. So using the example above with ABC as the look up value the results
> would look like;
>
> Horse
> Dog
> Mouse
>
> Any help with this very appreciated. Thanks.
From: Chip Pearson on 28 Mar 2010 21:01
On Sheet2, cell A1, type the following array formula

=OFFSET(Sheet1!$A$1,MATCH(SMALL(IF(Sheet1!$A$1:$A$12="a",Sheet1!$B$1:$B$12,MAX(Sheet1!$B$1:$B$12)+1),COLUMN()),Sheet1!$B$1:$B$12,0)-1,2,1,1)

and press CTRL SHIFT ENTER. Change the literal "a" lookup and change
the A1:A12 and B1:B12 to the appropriate ranges. Copy this formula to
the right from A1 on sheet2 for as many values as you want to return.
A1 will be the minimum, B1 the second smallest, C1 the third smallest,
etc.

This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Sun, 28 Mar 2010 14:48:01 -0700, Andrew
<Andrew(a)discussions.microsoft.com> wrote:

>Hi there,
>
>I have three colums of data in a worksheet from A1 to C5, example;
>
>Column A Column B Column C
>ABC 5/2/10 02:00 Dog
>DEF 5/2/10 02:15 Cat
>ABC 5/2/10 03:47 Mouse
>GHI 5/2/10 01:20 Bird
>ABC 5/2/10 00:11 Horse
>
>What I need to do in a seperate worksheet in A1 is to look up a value in
>column A, find its lowest figure in column B and return the corresponding
>value from column C. Then in A2 vlook up the same value in column A, find the
>SECOND lowest figure in column B and return the corresponding value from
>column C. Then in A3 the same but returning the THIRD lowest figure and on
>and on. So using the example above with ABC as the look up value the results
>would look like;
>
>Horse
>Dog
>Mouse
>
>Any help with this very appreciated. Thanks.
 | 
Pages: 1
Prev: Track Changes Colour
Next: excel sorting