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?

 

[jennac4444]

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

Sorry, I responded to the wrong post

Both lists are ascending

I used your formula and received a false

But here is an example of what each sheet has listed
First one is on sheet one, the second cell is the list I'm comparing to
<TABLE style="WIDTH: 323pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=431 x:str><COLGROUP><COL style="WIDTH: 275pt; mso-width-source: userset; mso-width-alt: 13421" width=367><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 275pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2903047 class=xl24 height=17 width=367 align=left>ÅBO AKADEMI, FINLAND</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 align=left>Åbo Akademi (ÅA)</TD></TR></TBODY></TABLE>

 

[jennac4444]

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

This is going to sound goofy, but I haven't copied and pasted a macro before... I've recorded but haven't loaded one. Can you let me know how to do it? It sounds like this would do the trick

Thanks much

 

[Aladin Akyurek]

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

Sorry, I responded to the wrong post

Both lists are ascending

I used your formula and received a false

But here is an example of what each sheet has listed
First one is on sheet one, the second cell is the list I'm comparing to
<TABLE style="WIDTH: 323pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=431 x:str><COLGROUP><COL style="WIDTH: 275pt; mso-width-source: userset; mso-width-alt: 13421" width=367><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 275pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2903047 class=xl24 height=17 width=367 align=left>ÅBO AKADEMI, FINLAND</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 align=left>Åbo Akademi (ÅA)</TD></TR></TBODY></TABLE>

If these two values are considered as matching, you'll need a dfferent approach:

http://www.mrexcel.com/forum/showthread.php?t=195635

By the way, if the range to match is sorted in ascending order...

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

will be faster than a VLOOKUP formula with exact match.

_________________
Posted from Istanbul.

 

[T. Valko]

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

Sorry, I responded to the wrong post

Both lists are ascending

I used your formula and received a false

But here is an example of what each sheet has listed
First one is on sheet one, the second cell is the list I'm comparing to
<TABLE style="WIDTH: 323pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=431 border=0 x:str><COLGROUP><COL style="WIDTH: 275pt; mso-width-source: userset; mso-width-alt: 13421" width=367><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_2903047 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 275pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=left width=367 height=17>ÅBO AKADEMI, FINLAND</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=left width=64>Åbo Akademi (ÅA)</TD></TR></TBODY></TABLE>

This is going to sound goofy, but I haven't copied and pasted a macro before... I've recorded but haven't loaded one. Can you let me know how to do it? It sounds like this would do the trick

Thanks much

Now that we see a sample I'm not so sure that TrimAll macro is what you need.

To Excel, those are 2 significantly different values.

You need to do what is known as "fuzzy matching".

There's an extensive discussion of it here:

http://www.mrexcel.com/forum/showthread.php?t=195635

 

[jennac4444]

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

alright, I must be slower than most, I followed the instructions, pasted the code to the module saved it, closed out, hit play button and nothing...

 

[T. Valko]

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

alright, I must be slower than most, I followed the instructions, pasted the code to the module saved it, closed out, hit play button and nothing...

Sometimes the actions (results) of the TrimAll macro are not obvious.

See my other reply regarding "fuzzy matching". I think that's what you need.

 

[Aladin Akyurek]

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

alright, I must be slower than most, I followed the instructions, pasted the code to the module saved it, closed out, hit play button and nothing...

The instruction on how to include TrimAll to your workbook is given in:

http://www.mrexcel.com/forum/showthread.php?t=69341 (Post #13)

The link I posted on "fuzzy matching" is probably not affected significantly by the unintended spaces.

_________________
Posted from Istanbul.