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.

 

[VoG]

See if this helps http://www.contextures.com/xlFunctions02.html#trouble

 

[CharlesChuckieCharles]

Not going to down load

VLOOKUP(lookupvalue, array of values, return column, FALSE=EXACT MATCH, TRUE = CLOSE MATCH as in Help )

Its probably set to FALSE at the moment

Also check for leading and training spaces both in the lookupValue and the array of values

also change one of the array of values so it exactly matches a lookup value just to check the vlookup works!!

 

[zaska]

Sir,

I tried the following but still the vlookup values are not accurate.

=vlookup(Trim(A2),J:K,2,0)

=Vlookup(--A2,J:K,2,0)

=Vlookup(A2,J:K,2,1)

=Vlooku(Trim(A2,J:K,2,1)

I also tried changing the Lookup table to Proper Case.


None of the above formula's worked for me. The data is accurate only for few cells.

Kindly look at the uploaded worksheet.

Thank you

 

[VoG]

Maybe

=vlookup(--Trim(A2),J:K,2,0)

 

[zaska]

Sir,

After Using =vlookup(--Trim(A2),J:K,2,0) i got #Value Error and not even a single cell contained the data.

Can i use anyother formulas instead of Vlookup?

Thank you

 

[VoG]

This works for some of them

=vlookup(Trim(A2),J:K,2,0)

Try selecting columns A, J and K and running the TrimAll macro

http://dmcritchie.mvps.org/excel/join.htm

 

[CharlesChuckieCharles]

IF your partial Keys are less than the value appearing in you lookup array then vlookup won't find anything if you just want to get rid of the #N/A's then use

IF(ISNA(VLOOKUP(......)."",VLOOKUP(......))

 

[zaska]

Sir,

I tried removing the spaces in both lookupvalues and lookuptable and it did work to some extent.

Still i am having errors with few values.

Can i change vlookuformula to look only the first 5 letters of lookupvalue.

Example of one conflict situation.

MothersonSumiSystemsLtd. MothersonSumiSystemsLt

Thank you

 

[Jonmo1]

Can i change vlookuformula to look only the first 5 letters of lookupvalue.
Thank you

Can your lookup table be sorted ascending by column J?

Then try

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