# Re:Why does lookup for cloasest match return wrong info? Excel 2003

#### jennac4444

##### New Member
Re:Why does lookup for cloasest match return wrong info? Excel 2003

Does anyone know why a lookup function for closest match returns a wrong name from a list, and then three cells down, there is an exact match that is returned in something not even close?

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Re: Why does lookup for cloasest match return wrong info? Excel 2003

Does anyone know why a lookup function for closest match returns a wrong name from a list, and then three cells down, there is an exact match that is returned in something not even close?

Care to post the formula you have?

Re: Why does lookup for cloasest match return wrong info? Excel 2003

We need to see a bit more. The list is sorted in the right direction as in the help file?

Re: Why does lookup for cloasest match return wrong info? Excel 2003

Does anyone know why a lookup function for closest match returns a wrong name from a list, and then three cells down, there is an exact match that is returned in something not even close?
Without seeing your formula and the data it's hard to tell why you got a particular result.

In general...

For a "closest" match the data MUST be sorted on the lookup value range in ascending order.

Also, to be exact, "closest" actually means the closest match that is less than or equal to the lookup value.

Re: Why does lookup for cloasest match return wrong info? Excel 2003

Hi - yes the list is sorted in ascending...

It's a real simple formula: =VLOOKUP(A1552,Sheet2!\$A\$2:\$A\$14966,1,TRUE)

I've cleaned the two worksheets I'm comparing w/ CLEAN fx, TRIM well.

One sheet has 9k+ listed schools, the one I'm comparing it to has 15k+, and there are some exact matches that are'nt aligning correctly, they are listed like three rows down or up from the exact match.

Re: Why does lookup for cloasest match return wrong info? Excel 2003

Hi - yes the list is sorted in ascending...

It's a real simple formula: =VLOOKUP(A1552,Sheet2!\$A\$2:\$A\$14966,1,TRUE)

I've cleaned the two worksheets I'm comparing w/ CLEAN fx, TRIM well.

One sheet has 9k+ listed schools, the one I'm comparing it to has 15k+, and there are some exact matches that are'nt aligning correctly, they are listed like three rows down or up from the exact match.

Does...

_________________
Posted from Istanbul.

Re: Why does lookup for cloasest match return wrong info? Excel 2003

Hi - yes the list is sorted in ascending...

It's a real simple formula: =VLOOKUP(A1552,Sheet2!\$A\$2:\$A\$14966,1,TRUE)

I've cleaned the two worksheets I'm comparing w/ CLEAN fx, TRIM well.

One sheet has 9k+ listed schools, the one I'm comparing it to has 15k+, and there are some exact matches that are'nt aligning correctly, they are listed like three rows down or up from the exact match.

Typically, you use a "closest" match when you're dealing with numeric data.

Try it like this and see if it makes a difference:

=VLOOKUP(A1552,Sheet2!\$A\$2:\$A\$14966,1,0)

Or...

=IF(COUNT(MATCH(A1552,Sheet2!\$A\$2:\$A\$14966,0)),A1552,"")

Re: Why does lookup for cloasest match return wrong info? Excel 2003

Hi - yes the list is sorted in ascending...

It's a real simple formula: =VLOOKUP(A1552,Sheet2!\$A\$2:\$A\$14966,1,TRUE)

Which list is sorted ascending?

Just for clarity sake, the range I Highlighted red must be sorted ascending..

The only reasonable explaination(s) for that formula to return a non exact match when an exact match exists are:
1. The data is not sorted ascending.
2. There actually is NOT an exact match. Check for exact spelling, extra spaces etc...

What does this return
=A1552=Sheet2!A2
Where A2 is a cell you believe to be the exact match.

Re: Why does lookup for cloasest match return wrong info? Excel 2003

No, that's the odd thing, I tried it on an exact match and it returned not found. Is there something else I should do to scrub the data?

Re: Why does lookup for cloasest match return wrong info? Excel 2003

No, that's the odd thing, I tried it on an exact match and it returned not found. Is there something else I should do to scrub the data?

Where does this data come from?

The macro at this website will remove all leading/trailing and multiple interspersed char 32 space characters. It will also remove and/or convert char 160 non breaking spaces into standard char 32 space characters. It will work on text or numbers and the numbers will be converted to true numeric numbers.

I use this macro dozens of times every single day! It's a real time saver.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Replies
15
Views
486
Replies
3
Views
137
Replies
1
Views
122
Replies
1
Views
156
Replies
58
Views
2K

1,203,027
Messages
6,053,119
Members
444,640
Latest member
Dramonzo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back