searching

I have two columns. I want to search the first column for the largest number
and return the value in the next cell.

Then I would like to search the first column for the second largest number
and return the value in the next cell.

I would like to do this without sorting the data!!

Is this possible?

For the cell below
=Offset(A1,Match(Large(A:A,1),A:A,0),0)

=Offset(A1,Match(Large(A:A,2),A:A,0),0)

for the cell to the Right
=Offset(A1,Match(Large(A:A,1),A:A,0)-1,1)

=Offset(A1,Match(Large(A:A,2),A:A,0)-1,1)

If the first and second numbers are equal, then this will give the wrong
answer.

--
Regards,
Tom Ogilvy

"Tracy A" <Tracy A@discussions.microsoft.com> wrote in message
news:110211DC-276F-4D7E-B7D8-44FDD9DFF449@microsoft.com...
> I have two columns. I want to search the first column for the largest
number
> and return the value in the next cell.
>
> Then I would like to search the first column for the second largest number
> and return the value in the next cell.
>
> I would like to do this without sorting the data!!
>
> Is this possible?
>

For the cell below
=Offset(A1,Match(Large(A:A,1),A:A,0),0)

=Offset(A1,Match(Large(A:A,2),A:A,0),0)

for the cell to the Right
=Offset(A1,Match(Large(A:A,1),A:A,0)-1,1)

=Offset(A1,Match(Large(A:A,2),A:A,0)-1,1)

If the first and second numbers are equal, then this will give the wrong
answer.

--
Regards,
Tom Ogilvy

"Tracy A" <Tracy A@discussions.microsoft.com> wrote in message
news:110211DC-276F-4D7E-B7D8-44FDD9DFF449@microsoft.com...
> I have two columns. I want to search the first column for the largest
number
> and return the value in the next cell.
>
> Then I would like to search the first column for the second largest number
> and return the value in the next cell.
>
> I would like to do this without sorting the data!!
>
> Is this possible?
>

To take ties into consideration, assuming that Column A contains your
numbers and you want to return the corresponding value in Column B,
try...

C1, copied down:

=INDEX($B$1:$B$100,MATCH(LARGE($A$1:$A$100-ROW($A$1:$A$100)/10^10,ROWS($C
$1:C1)),$A$1:$A$100-ROW($A$1:$A$100)/10^10,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <110211DC-276F-4D7E-B7D8-44FDD9DFF449@microsoft.com>,
"Tracy A" <Tracy A@discussions.microsoft.com> wrote:

> I have two columns. I want to search the first column for the largest number
> and return the value in the next cell.
>
> Then I would like to search the first column for the second largest number
> and return the value in the next cell.
>
> I would like to do this without sorting the data!!
>
> Is this possible?

To take ties into consideration, assuming that Column A contains your
numbers and you want to return the corresponding value in Column B,
try...

C1, copied down:

=INDEX($B$1:$B$100,MATCH(LARGE($A$1:$A$100-ROW($A$1:$A$100)/10^10,ROWS($C
$1:C1)),$A$1:$A$100-ROW($A$1:$A$100)/10^10,0))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <110211DC-276F-4D7E-B7D8-44FDD9DFF449@microsoft.com>,
"Tracy A" <Tracy A@discussions.microsoft.com> wrote:

> I have two columns. I want to search the first column for the largest number
> and return the value in the next cell.
>
> Then I would like to search the first column for the second largest number
> and return the value in the next cell.
>
> I would like to do this without sorting the data!!
>
> Is this possible?