I am trying to match a Lookup Value with the largest of the potential
Responses in a second database. =vlookup(B2,data range,4,False)

I understood that VLookup will search until it finds the first match for
what is in B2 and then return the value in the 4th column of the data range.
So I sorted the data range so that the Search Column was ascending and the
4th, value column was descending. This way, the first match would return the
highest value.

For some reason though, the first match is being skipped and the data from a
duplicate (lower value) match is being grabbed.
..
..
..
Scot 5.0
Scot 4.0
..
..

The result is 4.0

here is my formula in the cell:
=VLOOKUP(B607,'[array data.xls]Orig data'!$B$1:$G$5365,4,FALSE)

B1:G5365 is sorted ascending by B and descending by column 4.

What am I missing? Thanks for your help.

Perhaps the "Scot" that you are looking for is really "Scot " in B607
(i.e. with a space after it), and perhaps the second "Scot" that you
see in the table is also really "Scot ". VLOOKUP is giving you an
exact match, and so it is important that you get rid of leading and
trailing spaces, both in your table as well as in the item you are
looking for - use TRIM( ... ) to get rid of them.

Hope this helps.

Pete

On Dec 7, 4:53 pm, Pogue wrote:
I am trying to match a Lookup Value with the largest of the potential
Responses in a second database. =vlookup(B2,data range,4,False)

I understood that VLookup will search until it finds the first match for
what is in B2 and then return the value in the 4th column of the data range.
So I sorted the data range so that the Search Column was ascending and the
4th, value column was descending. This way, the first match would return the
highest value.

For some reason though, the first match is being skipped and the data from a
duplicate (lower value) match is being grabbed.
.
.
.
Scot 5.0
Scot 4.0
.
.

The result is 4.0

here is my formula in the cell:
=VLOOKUP(B607,'[array data.xls]Orig data'!$B$1:$G$5365,4,FALSE)

B1:G5365 is sorted ascending by B and descending by column 4.

What am I missing? Thanks for your help.

Are there any spaces at the end of the names possibly, causing it to mess up?
When it set it up on my computer, it works okay.
--
Best Regards,

Luke M


"Pogue" wrote:

I am trying to match a Lookup Value with the largest of the potential
Responses in a second database. =vlookup(B2,data range,4,False)

I understood that VLookup will search until it finds the first match for
what is in B2 and then return the value in the 4th column of the data range.
So I sorted the data range so that the Search Column was ascending and the
4th, value column was descending. This way, the first match would return the
highest value.

For some reason though, the first match is being skipped and the data from a
duplicate (lower value) match is being grabbed.
.
.
.
Scot 5.0
Scot 4.0
.
.

The result is 4.0

here is my formula in the cell:
=VLOOKUP(B607,'[array data.xls]Orig data'!$B$1:$G$5365,4,FALSE)

B1:G5365 is sorted ascending by B and descending by column 4.

What am I missing? Thanks for your help.

in the two search cells...it still bypasses the first one. Frustrating.
Thanks for the help.


"Luke M" wrote:

Are there any spaces at the end of the names possibly, causing it to mess up?
When it set it up on my computer, it works okay.
--
Best Regards,

Luke M


"Pogue" wrote:

I am trying to match a Lookup Value with the largest of the potential
Responses in a second database. =vlookup(B2,data range,4,False)

I understood that VLookup will search until it finds the first match for
what is in B2 and then return the value in the 4th column of the data range.
So I sorted the data range so that the Search Column was ascending and the
4th, value column was descending. This way, the first match would return the
highest value.

For some reason though, the first match is being skipped and the data from a
duplicate (lower value) match is being grabbed.
.
.
.
Scot 5.0
Scot 4.0
.
.

The result is 4.0

here is my formula in the cell:
=VLOOKUP(B607,'[array data.xls]Orig data'!$B$1:$G$5365,4,FALSE)

B1:G5365 is sorted ascending by B and descending by column 4.

What am I missing? Thanks for your help.

Try retyping Scot in both the first cell in the lookup table and B607.

If that doesn't work (since you've corrected the spelling error in one or both
of the cells), look for another Scot in the table--maybe in a hidden row (is the
table filtered?).

Pogue wrote:

in the two search cells...it still bypasses the first one. Frustrating.
Thanks for the help.

"Luke M" wrote:

Are there any spaces at the end of the names possibly, causing it to mess up?
When it set it up on my computer, it works okay.
--
Best Regards,

Luke M


"Pogue" wrote:

I am trying to match a Lookup Value with the largest of the potential
Responses in a second database. =vlookup(B2,data range,4,False)

I understood that VLookup will search until it finds the first match for
what is in B2 and then return the value in the 4th column of the data range.
So I sorted the data range so that the Search Column was ascending and the
4th, value column was descending. This way, the first match would return the
highest value.

For some reason though, the first match is being skipped and the data from a
duplicate (lower value) match is being grabbed.
.
.
.
Scot 5.0
Scot 4.0
.
.

The result is 4.0

here is my formula in the cell:
=VLOOKUP(B607,'[array data.xls]Orig data'!$B$1:$G$5365,4,FALSE)

B1:G5365 is sorted ascending by B and descending by column 4.

What am I missing? Thanks for your help.


--

Dave Peterson

This wasn't the problem. There are only the two instances of Scot.
Looking at the datasheet shows that the sort works - meaning the higher
value Scot is first.

I copied and pasted the lookup value into these cells to ensure they match.

All are formatted as text, with no extra spaces or anything.

???

Thanks,
Joe

"Dave Peterson" wrote:

Try retyping Scot in both the first cell in the lookup table and B607.

If that doesn't work (since you've corrected the spelling error in one or both
of the cells), look for another Scot in the table--maybe in a hidden row (is the
table filtered?).

Pogue wrote:

in the two search cells...it still bypasses the first one. Frustrating.
Thanks for the help.

"Luke M" wrote:

Are there any spaces at the end of the names possibly, causing it to mess up?
When it set it up on my computer, it works okay.
--
Best Regards,

Luke M


"Pogue" wrote:

I am trying to match a Lookup Value with the largest of the potential
Responses in a second database. =vlookup(B2,data range,4,False)

I understood that VLookup will search until it finds the first match for
what is in B2 and then return the value in the 4th column of the data range.
So I sorted the data range so that the Search Column was ascending and the
4th, value column was descending. This way, the first match would return the
highest value.

For some reason though, the first match is being skipped and the data from a
duplicate (lower value) match is being grabbed.
.
.
.
Scot 5.0
Scot 4.0
.
.

The result is 4.0

here is my formula in the cell:
=VLOOKUP(B607,'[array data.xls]Orig data'!$B$1:$G$5365,4,FALSE)

B1:G5365 is sorted ascending by B and descending by column 4.

What am I missing? Thanks for your help.


--

Dave Peterson

Do you see Calculate in the statusbar?

Maybe calculation is set to manual and the workbook hasn't recalced since you
last sorted.

I've never seen =vlookup() get confused and return anything but the first match.

Either the values don't really match or there's a entry above it--or your
workbook needs to recalc.

Pogue wrote:

This wasn't the problem. There are only the two instances of Scot.
Looking at the datasheet shows that the sort works - meaning the higher
value Scot is first.

I copied and pasted the lookup value into these cells to ensure they match.

All are formatted as text, with no extra spaces or anything.

???

Thanks,
Joe

"Dave Peterson" wrote:

Try retyping Scot in both the first cell in the lookup table and B607.

If that doesn't work (since you've corrected the spelling error in one or both
of the cells), look for another Scot in the table--maybe in a hidden row (is the
table filtered?).

Pogue wrote:

in the two search cells...it still bypasses the first one. Frustrating.
Thanks for the help.

"Luke M" wrote:

Are there any spaces at the end of the names possibly, causing it to mess up?
When it set it up on my computer, it works okay.
--
Best Regards,

Luke M


"Pogue" wrote:

I am trying to match a Lookup Value with the largest of the potential
Responses in a second database. =vlookup(B2,data range,4,False)

I understood that VLookup will search until it finds the first match for
what is in B2 and then return the value in the 4th column of the data range.
So I sorted the data range so that the Search Column was ascending and the
4th, value column was descending. This way, the first match would return the
highest value.

For some reason though, the first match is being skipped and the data from a
duplicate (lower value) match is being grabbed.
.
.
.
Scot 5.0
Scot 4.0
.
.

The result is 4.0

here is my formula in the cell:
=VLOOKUP(B607,'[array data.xls]Orig data'!$B$1:$G$5365,4,FALSE)

B1:G5365 is sorted ascending by B and descending by column 4.

What am I missing? Thanks for your help.


--

Dave Peterson


--

Dave Peterson