How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9658
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

I am having an issue with the vlookup formula that I am using.

Resolved Question:

I am having an issue with the vlookup formula that I am using. I have used it successfully on several occasions however, I am working with two different documents from two different sources and I am stumped on why I keep getting the #N/A value.

I have sorted the criteria, I have ensured the proper format and I have walked through each step of my formula and I cannot find the issue. I need help with this one.
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  John D replied 2 years ago.

Hi,

 

Before I ask you to send the file could you please paste the formula here in your reply so I can check the syntax

 

 

Customer: replied 2 years ago.
Hi John,

The formula that I am using is

=vlookup(A28,'[incidents by vendor by day - dm.xlsx]Sheet1'!$A:$B,false)

Best regards,

Davon
Expert:  John D replied 2 years ago.

Thanks

 

Ok the column_index parameter is missing (i.e. 3rd parameter), assuming of course that that file exists.

 

Try this, then you can replace the "1" in the formula with the actual column index that you want to pull data from

 

=Vlookup(A28,'[incidents by vendor by day - dm.xlsx]Sheet1'!$A:$B, 1, false)


Hope this helps. Let me know if you have any questions, and thank you for clicking Accept if you are satisfied with the solution

 

Customer: replied 2 years ago.
Hi John,

I did specify the range and in this case it would be the value of 2 instead of 1. It still does not work.

=VLOOKUP(A28,'[incidents by vendor by day - dm.xlsx]Sheet1'!$A:$B,2,FALSE)

Expert:  John D replied 2 years ago.

Ok try this

 

Type Joe in A28 in the current file

 

Then open the source file and type Joe in A5, and type Doe in B5

 

What does the formula return?

 

 

 

Customer: replied 2 years ago.
The formula returned the correct value for the look up.
Expert:  John D replied 2 years ago.

Ok that means the formula is correct and the problem is with your data, most likely it is not able to find an exact match. Try replying the False with True to see if catches the next match

 

Hope this helps.

 

Thank you

 

 

Customer: replied 2 years ago.
Hi John,

I have tried Replacing the false with true and still does not work.

I have attempted to copy the text and conduct a search/find for values that I know are there with no luck. There aren't any issues with the spacing either.
Expert:  John D replied 2 years ago.

There must be an issue with the data. The only way I can resolve this is for me to have both files so I can thoroughly check the data. I don't mind doing that but I think at this point you need to accept so I make sure I get paid for my work thus far. You can add bonus upon completion if you like

 

Let me know if you need help uploading the files

 

Thank you

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9658
Experience: Bachelor of Science - Engineering Consultant
John D and 7 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.
Please provide instructions on uploading the files.

Thanks
Customer: replied 2 years ago.
John,

I have accepted the answer so that you are paid, I require the instructions to upload the files for review.

Expert:  John D replied 2 years ago.

Yes, thanks. I sent you the instructions a while ago but apparently they did not get posted!

 

Here it is again

 

Please go to http://www.wikisend.com/ and upload the file there (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.

 

If the file has sensitive information let me know before you upload it

 

Let me know in which cell that formula is

 

Customer: replied 2 years ago.
Hi John,

Unfortunately I am getting error message 500 when I attempt to upload the files on the wiki site that you've referred me to.

Is there at another method that we can use to get the files over to you?

Thanks
Expert:  John D replied 2 years ago.

Ok try this site instead

 

http://www.sendspace.com/

 

Expert:  John D replied 2 years ago.

Were you able to upload the file

 

 

Customer: replied 2 years ago.
I need your email address to be able to use that site. I will not be able to upload without it.
Expert:  John D replied 2 years ago.

No you do not need to use the email box. Just click Browse, select your file, click Upload, then copy the download link that you will get and post here

 

 

Customer: replied 2 years ago.
Ok it's done, here's the link

http://www.sendspace.com/filegroup/IHFo5Px7Q9tkJc2uPjaroQ

Expert:  John D replied 2 years ago.

So where is the source file that the formula is pulling the data from?

 

Customer: replied 2 years ago.
It's the second file that I included in the upload it's the first tab second column
Customer: replied 2 years ago.
Here's a second link for the source file

http://www.sendspace.com/file/s6g19b

Expert:  John D replied 2 years ago.

I am looking at first file...

 

So the lookup table (source data) is in columns A and B on that sheet

 

And the formula is in cell D5 on the same sheet

 

And the name to look for is in A5 on the same sheet

 

Correct?

 

 

Customer: replied 2 years ago.
Actually no, thats from what we did earlier, disregard those cells.

The source data worksheet only has 2 columns in it. The sheet that I want to populate the data has column D.

Cell row 28 on the sheet that contains column D has the formula in it (E28) and its attempting to lookup the data on the second file I sent in column B (the sheet containing only two columns).
Expert:  John D replied 2 years ago.

Here you go

 

Ok the reason you are getting the #N/A! error is because no matches could be found and that is basically because the names on that sheet have leading spaces. I modified the formula so it will ignore these spaces

 

Here is the file with the formulas in the new yellow cells. Make sure the source file is open so the formulas can be pull the values

 

Attachment: 2012-04-02_201238_compiled_contact_center_and_agent_activity_summary_data_from_verizon1.xlsx

 

Hope this is ok.

 

Please make sure to remove the Neutral Feedback that you left me earlier which is not really fair or warranted.

 

Thank you

 

 

Customer: replied 2 years ago.
Thank you John for your help, I really appreciate it.
Expert:  John D replied 2 years ago.
You are welcome. Glad to help

(BTW to remove or replace the Neutral Feedback you can just a message to support@justanswer.com simply just asking to remove it.)

Thanks again

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions