Vlookup not returning information

  • Thread starter Thread starter dannaz
  • Start date Start date
D

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.
 
Check out the formual array range .. Post sample data and formula which is
erroneous.

If this post helps click Yes
 
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
 
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
 
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
 
--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
 
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
 
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
 
Back
Top