Vlookup not returning information

[dannaz]

In spreadsheet A I enter a part number. I then have a formula that does a
look up of that part number in spreadsheet B and returns the description,
cost and price of the part number back into spreadsheet A.

This has been working perfectly until today. I found an error with a part
number in spreadsheet B and corrected the error (changed from ":CFF-8" to
"CFF-8"). I have done corrections in the past and added items to spreadsheet
B with no problems.

However, today after correcting the part number in spreadsheet B, all I get
in spreadsheet A for description, cost or price is "#N/A". If I change the
part number back to the old one, then everything works fine. Even if I
insert a new line with the correct part number, it still will not return any
information.

Any ideas what is going on?

Thanks in advance for any help.

 

[Jacob Skaria]

Check out the formual array range .. Post sample data and formula which is
erroneous.

If this post helps click Yes

 

[dannaz]

Array range is fine.
if I enter ":CFF-8" in cell os spreadsheet A, then I get the proper
description of this part from spreadsheet B. However, if I go into
spreadsheet B and change te part numberfrom ":CFF-8" to "CFF-8), enter "CFF-8
" into cell in spreadsheet A, all I get for a description is #N/A.

Here is the formula that I am using for the lookup:
=IF($A53>0,VLOOKUP($A53,'C:\Users\Dan\Documents\Excel Files\Information for
project costing sheets.xlsm'!Inventory,2,FALSE)," ")

Thanks

Dan

 

[Jacob Skaria]

OK. Check whether you are having a space before or after ":CFF-8" in cell $A53.


Inorder to handle the additional : in your lookup table you can adjust the
vlookup to the below...which will lookup "CFF-8" and return the results for
":CFF-8" ....

=VLOOKUP("*" & TRIM($A53) & "*",Sheet2!A:B,2,0)

If this post helps click Yes

 

[dannaz]

No spaces before or after. I have tried copying the part number from
spreadsheet B and inserting it into spreadsheet A to make sure the number are
identical. Still will not return the description. Also, if I insert a new
line in spreadsheet B and enter a new part number and description, then try
to recall this in spreadsheet A, it still will not find that new part number
description.

Dan

 

[Jacob Skaria]

--Have you tried the below version
=VLOOKUP("*" & TRIM($A53) & "*",Sheet2!A:B,2,0)

--I am sure this is something simple which we are unable to track. Check
your named range; Copy paste a small set of data to another workbook and try
out sample VLOOKUPS() to find whats happening..

If this post helps click Yes

 

[dannaz]

Yes Jacob it was something simple, very simple. Somehow, and the classic
excuse - I didn't do it, my spreadsheet B got saved to the main Documents
folder rather than my Excel folder (where the Vlookup was looking for the
file) and I have been editing the file in the main folder. What is strange
is, i wondered if I had two of the Spreadsheet B files and did a search for
the file and only one showed up. The only way that i just discovered this
problem is when I went to open the file using the recent documents tab, I was
slower than usual when clicking on it and the directory information showed up
of where the file was located. I also had to click on the Documents folder
three times before this file showed up in the listing.

Thanks for your prompt help.

Very sincerely

Dan

 

[Jacob Skaria]

Good to know that..

Now it is time to try out this so that you dont need to edit the values in
ColB...(hopefully)

=VLOOKUP("*" & TRIM($A53) & "*",...

If this post helps click Yes