I have a column of names with 5 rows of scores. When I sort by the
names and use the VLOOKUP to get the scores the functions works as
expected. When I sort for the column that has the highest score (found
using the MAX function) the names are not in alphabetical order. The
VLOOKUP function stops working when it gets to name that are out of
aphetically order.

Will Work
SORT
Bob | 23026|24512|12489|26541|
Don |50647 |21489|54735|24864|

Will Not Work SORT
Don |50647 |21489|54735|99999|
Bob | 23026|24512|12489|26541|

It is always helpful to show the formula one is asking about! My guess is
that you have the incorrect last argument for an exact match which is what
you need with an unsorted list.
This is taken from Help:

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)
Range_lookup A logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match:

a.. If TRUE or omitted, an exact or approximate match is returned. If an
exact match is not found, the next largest value that is less than
lookup_value is returned.
The values in the first column of table_array must be placed in ascending
sort order; otherwise, VLOOKUP may not give the correct value. You can put
the values in ascending order by choosing the Sort command from the Data
menu and selecting Ascending. For more information, see Default sort orders.

b.. If FALSE, VLOOKUP will only find an exact match. In this case, the
values in the first column of table_array do not need to be sorted. If there
are two or more values in the first column of table_array that match the
lookup_value, the first value found is used. If an exact match is not found,
the error value #N/A is returned.
----
Do you have FALSE as your last argument?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JG" wrote in message
ups.com...
I have a column of names with 5 rows of scores. When I sort by the
names and use the VLOOKUP to get the scores the functions works as
expected. When I sort for the column that has the highest score (found
using the MAX function) the names are not in alphabetical order. The
VLOOKUP function stops working when it gets to name that are out of
aphetically order.

Will Work
SORT
Bob | 23026|24512|12489|26541|
Don |50647 |21489|54735|24864|

Will Not Work SORT
Don |50647 |21489|54735|99999|
Bob | 23026|24512|12489|26541|

=VLOOKUP(AE344,$A$4:$AB$15,2)


Bernard Liengme wrote:
It is always helpful to show the formula one is asking about! My guess is
that you have the incorrect last argument for an exact match which is what
you need with an unsorted list.
This is taken from Help:

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)
Range_lookup A logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match:

a.. If TRUE or omitted, an exact or approximate match is returned. If an
exact match is not found, the next largest value that is less than
lookup_value is returned.
The values in the first column of table_array must be placed in ascending
sort order; otherwise, VLOOKUP may not give the correct value. You can put
the values in ascending order by choosing the Sort command from the Data
menu and selecting Ascending. For more information, see Default sort orders.

b.. If FALSE, VLOOKUP will only find an exact match. In this case, the
values in the first column of table_array do not need to be sorted. If there
are two or more values in the first column of table_array that match the
lookup_value, the first value found is used. If an exact match is not found,
the error value #N/A is returned.
----
Do you have FALSE as your last argument?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JG" wrote in message
ups.com...
I have a column of names with 5 rows of scores. When I sort by the
names and use the VLOOKUP to get the scores the functions works as
expected. When I sort for the column that has the highest score (found
using the MAX function) the names are not in alphabetical order. The
VLOOKUP function stops working when it gets to name that are out of
aphetically order.

Will Work
SORT
Bob | 23026|24512|12489|26541|
Don |50647 |21489|54735|24864|

Will Not Work SORT
Don |50647 |21489|54735|99999|
Bob | 23026|24512|12489|26541|

FALSE worked thank you !


JG wrote:
=VLOOKUP(AE344,$A$4:$AB$15,2)


Bernard Liengme wrote:
It is always helpful to show the formula one is asking about! My guess is
that you have the incorrect last argument for an exact match which is what
you need with an unsorted list.
This is taken from Help:

VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)
Range_lookup A logical value that specifies whether you want VLOOKUP to
find an exact match or an approximate match:

a.. If TRUE or omitted, an exact or approximate match is returned. If an
exact match is not found, the next largest value that is less than
lookup_value is returned.
The values in the first column of table_array must be placed in ascending
sort order; otherwise, VLOOKUP may not give the correct value. You can put
the values in ascending order by choosing the Sort command from the Data
menu and selecting Ascending. For more information, see Default sort orders.

b.. If FALSE, VLOOKUP will only find an exact match. In this case, the
values in the first column of table_array do not need to be sorted. If there
are two or more values in the first column of table_array that match the
lookup_value, the first value found is used. If an exact match is not found,
the error value #N/A is returned.
----
Do you have FALSE as your last argument?
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JG" wrote in message
ups.com...
I have a column of names with 5 rows of scores. When I sort by the
names and use the VLOOKUP to get the scores the functions works as
expected. When I sort for the column that has the highest score (found
using the MAX function) the names are not in alphabetical order. The
VLOOKUP function stops working when it gets to name that are out of
aphetically order.

Will Work
SORT
Bob | 23026|24512|12489|26541|
Don |50647 |21489|54735|24864|

Will Not Work SORT
Don |50647 |21489|54735|99999|
Bob | 23026|24512|12489|26541|