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

[jennac4444]

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?

 

[Aladin Akyurek]

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?

 

[p45cal]

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?

 

[T. Valko]

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.

 

[jennac4444]

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.

Thanks for replying

 

[Aladin Akyurek]

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.

Thanks for replying

Does...

=IF(VLOOKUP(A1552,Sheet2!$A$2:$A$14966,1,1)=A1552,A1552,"Not Found")

satisfy your goal?

_________________
Posted from Istanbul.

 

[T. Valko]

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.

Thanks for replying

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,"")

 

[Jonmo1]

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.

 

[jennac4444]

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?

Thanks for your assistance

 

[T. Valko]

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?

Thanks for your assistance

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