Vlookup Fetching Error #N/A

[zaska]

Hi all ,

I am having problem with vlookup. It is fetching #N/A error eventhough the lookup table matches with most part of the lookupvalue.

I don't know whether if other formulas can be used for this scenario.

I have uploaded the excel sheet here..

http://www.4shared.com/document/99RepnDk/Lookup.html


Thank you for anyhelp.

 

[CharlesChuckieCharles]

This will use the first five of the lookupvalue

=VLOOKUP(LEFT(A1,5),.....

 

[zaska]

Sir,

The lookup table is sorted in ascending order and the following formula still turning error #N/A

=Vlookup(Left(A2,5),J:K,2,0)


Example:

<table width="285" border="0" cellpadding="0" cellspacing="0"><col width="285"><tr height="17"> <td class="xl24" style="height:12.75pt;width:214pt" width="285" height="17">SonaKoyoSteeringSystems
</td> </tr></table>
<table width="285" border="0" cellpadding="0" cellspacing="0"><col width="285"><tr height="17"> <td class="xl24" style="height:12.75pt;width:214pt" width="285" height="17">SonaKoyoSterSys


</td> </tr></table>

 

[Jonmo1]

You didn't use the formula I suggested...

Can your lookup table be sorted ascending by column J?

Then try

=VLOOKUP(LEFT(A2,5),J:K,2,TRUE)

 

[VoG]

Try a fuzzy vlookup http://www.mrexcel.com/forum/showthread.php?t=556697

 

[CharlesChuckieCharles]

=Vlookup(Left(A2,5),J:K,2,0)

with 0 as the last parameter = EXACT MATCH otherwise return #N/A

if you are going to use a partial match you need -1 or TRUE

either TRUE or is 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 return

 

[zaska]

Sir,

I tried the above formulas still getting errors & fuzzyvlookup logic is difficult for me.


Example :

<table width="285" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="285"></colgroup><tbody><tr height="17"> <td class="xl24" style="height:12.75pt;width:214pt" width="285" height="17">SonaKoyoSteeringSystems</td> </tr></tbody></table>
<table width="285" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="285"></colgroup><tbody><tr height="17"> <td class="xl24" style="height:12.75pt;width:214pt" width="285" height="17">SonaKoyoSterSys


</td> </tr></tbody></table>I tried =Vlookup(Left(A1,5),J:K,2,TRUE)

But i got incorrect data even though the first five Values match in the above example.

 

[zaska]

Hi,,

any help ??

 

[Aladin Akyurek]

Sir,

I tried the above formulas still getting errors & fuzzyvlookup logic is difficult for me.


Example :

<TABLE border=0 cellSpacing=0 cellPadding=0 width=285><COLGROUP><COL width=285></COLGROUP><TBODY><TR height=17><TD style="WIDTH: 214pt; HEIGHT: 12.75pt" class=xl24 height=17 width=285>SonaKoyoSteeringSystems</TD></TR></TBODY></TABLE>
<TABLE border=0 cellSpacing=0 cellPadding=0 width=285><COLGROUP><COL width=285></COLGROUP><TBODY><TR height=17><TD style="WIDTH: 214pt; HEIGHT: 12.75pt" class=xl24 height=17 width=285>SonaKoyoSterSys



</TD></TR></TBODY></TABLE>I tried =Vlookup(Left(A1,5),J:K,2,TRUE)

But i got incorrect data even though the first five Values match in the above example.

It looks like you should go with the fuzzy method...

Does one of the following...

=VLOOKUP(LEFT(A1)&"*",J:K,2,0)
 
=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"Not Found",
     VLOOKUP(LEFT(A1)&"*",J:K,2,0),VLOOKUP(A1,J:K,2,0)))
 
=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,
     VLOOKUP(LEFT(A1)&"*",J:K,2,0),VLOOKUP(A1,J:K,2,0)))

do any better?

 

[zaska]

Thank you sir,

I was finally able to finish my work with all your help.


Have a nice day

 

[Aladin Akyurek]

Thank you sir,

I was finally able to finish my work with all your help.


Have a nice day

You are welcome. Thanks for providing feedback.