Lookup Highest Number
 

I am trying to VLookup a column (Sheet 1, A) to find the highest number. Is
there a way to do this? I don't want to use auto filter.

Greg

Greg,

Use the MAX function in your VLOOKUP. SOmething like this untested one:

=VLOOKUP( MAX(A2:A30), Table, ColumnToReturn).

You could also sort the table, descending, on column A. That'd put the
highest one at the top of the list. Sorting a table for convenience on some
particular column is a perfectly acceptable thing to do, as long as you're
familiar with Excel's sorting mechanism, and its dangers.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"gregork" wrote in message
...
I am trying to VLookup a column (Sheet 1, A) to find the highest number. Is
there a way to do this? I don't want to use auto filter.

Greg

Earl,

What if that highest number in col A has more occurences, say six, and what
is wanted is the highest corresponding number in the ColumnToReturn which
will of course not necessarily be the "first" highest number found in col A.

So tha answer below would be 201.

A B

3 100
5 101
7 200
3 103
4 300
7 201
1 -600
2 201
7 104
5 301


Jack Sons
The Netherlands

"Earl Kiosterud" schreef in bericht
...
Greg,

Use the MAX function in your VLOOKUP. SOmething like this untested one:

=VLOOKUP( MAX(A2:A30), Table, ColumnToReturn).

You could also sort the table, descending, on column A. That'd put the
highest one at the top of the list. Sorting a table for convenience on
some
particular column is a perfectly acceptable thing to do, as long as you're
familiar with Excel's sorting mechanism, and its dangers.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"gregork" wrote in message
...
I am trying to VLookup a column (Sheet 1, A) to find the highest number.
Is
there a way to do this? I don't want to use auto filter.

Greg

Jack Sons wrote:
Earl,

What if that highest number in col A has more occurences, say six, and what
is wanted is the highest corresponding number in the ColumnToReturn which
will of course not necessarily be the "first" highest number found in col A.

So tha answer below would be 201.

A B

3 100
5 101
7 200
3 103
4 300
7 201
1 -600
2 201
7 104
5 301


Jack Sons
The Netherlands

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=MAX(VLookups(MAX(a1:a10),a1:b10,2))

Alan Beban

Alan,

They are; I downloaded them years ago. Stupid I did not think of
MAX(VLOOKUPS

Jack.

"Alan Beban" schreef in bericht
...
Jack Sons wrote:
Earl,

What if that highest number in col A has more occurences, say six, and
what
is wanted is the highest corresponding number in the ColumnToReturn
which
will of course not necessarily be the "first" highest number found in
col A.

So tha answer below would be 201.

A B

3 100
5 101
7 200
3 103
4 300
7 201
1 -600
2 201
7 104
5 301


Jack Sons
The Netherlands

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=MAX(VLookups(MAX(a1:a10),a1:b10,2))

Alan Beban

Thanks for the feedback. If you use them regularly you might want to
consider downloading them again; I have tried to make improvements and
add some functions over the years.

Alan Beban

Jack Sons wrote:
Alan,

They are; I downloaded them years ago. Stupid I did not think of
MAX(VLOOKUPS

Jack.

"Alan Beban" schreef in bericht
...

Jack Sons wrote:

Earl,

What if that highest number in col A has more occurences, say six, and

what

is wanted is the highest corresponding number in the ColumnToReturn

which

will of course not necessarily be the "first" highest number found in

col A.

So tha answer below would be 201.

A B

3 100
5 101
7 200
3 103
4 300
7 201
1 -600
2 201
7 104
5 301


Jack Sons
The Netherlands

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=MAX(VLookups(MAX(a1:a10),a1:b10,2))

Alan Beban