+ Reply to Thread
Results 1 to 8 of 8

VLOOKUP formula not functioning properly.

  1. #1
    Forum Contributor
    Join Date
    09-13-2012
    Location
    Atlanta, USA
    MS-Off Ver
    Office 2003, 2010
    Posts
    230

    VLOOKUP formula not functioning properly.

    Hi everyone,

    I have a strange problem. I consider myself an average if not decent Excel user, nested formulas, macros, basic VBA, etc, but I can't seem to get this VLOOKUP to do what I want. What has happened is I have converted a PDF of hospital success rates into an excel document. I have played with the formatting using TRIM and PROPER so that the hospital names/layouts match. If you run the VLOOKUP, it returns #N/A as if there is no value on the previous sheet. However, if you copy the hospital name from sheet 2, and search for it on sheet 1 (the same sheet as the VLOOKUP) it appears in the search function (which seems strange).

    I am wondering perhaps if the issue is that I converted it from a PDF and the names on Sheet1 are formatted/coded strangely because of that.

    Sheet1 Contains the Hospital Names and the success rates. Sheet 2 contains the list of hospitals I am trying to match via VLOOKUP as well as my failed VLOOKUP functions (which I believe are accurate). I have attached the file.

    Thanks for the help!

    Jake
    Attached Files Attached Files

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: VLOOKUP formula not functioning properly.

    You still have hard returns in your cells of Sheet1, and your VLOOKUP table_array range only goes to row 54.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: VLOOKUP formula not functioning properly.

    A couple of problems. The Sheet1 data has carriage returns in many cells... you can do and Edit|Replace (CTRL+H). in the Find What, hold the ALT key down and enter 010 using only the number pad on your keypad... then click Replace All.

    Then your Vlookup formula doesn't cover the full range. change to:

    =IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE),"")

    I added IFERROR to return blank cell instead of errors, where match not found.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Contributor
    Join Date
    09-13-2012
    Location
    Atlanta, USA
    MS-Off Ver
    Office 2003, 2010
    Posts
    230

    Re: VLOOKUP formula not functioning properly.

    I fixed the cells range going to 54, apparently that didn't upload my changes. Even if it goes to 3369 it doesn't pull the numbers.

    What is a hard return?

  5. #5
    Registered User
    Join Date
    05-28-2010
    Location
    London
    MS-Off Ver
    MS365 (PC) Version 2301
    Posts
    87

    Re: VLOOKUP formula not functioning properly.

    like hitting the ENTER button whilst holding Alt

  6. #6
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: VLOOKUP formula not functioning properly.

    Hard, paragraph, or carriage return is the character the "Enter" key places in text to start a new line.

  7. #7
    Forum Contributor
    Join Date
    09-13-2012
    Location
    Atlanta, USA
    MS-Off Ver
    Office 2003, 2010
    Posts
    230

    Re: VLOOKUP formula not functioning properly.

    Hard returns solved my problem. Thanks for the help.

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: VLOOKUP formula not functioning properly.

    You get an space at the end of your data.

    So the data are not indentic.

    See the formula in the sheet to check it.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1