+ Reply to Thread
Results 1 to 10 of 10

Why is this VLOOKUP returning N/A?

  1. #1
    Registered User
    Join Date
    02-28-2012
    Location
    Milwaukee
    MS-Off Ver
    Excel 2003
    Posts
    17

    Why is this VLOOKUP returning N/A?

    I have a VLOOKUP trying to pull in industries for certain securities. I have no idea why this VLOOKUP is not working. It seems to work when you paste the CUSIP from 'Sheet1' over the exact same CUSIP from the 'Original Tab'. The manual copy and pasting of the CUSIP and I would really like to avoid that especially since they are the exact same numbers - but the VLOOKUP does not seem to be reading it.

    Any help on this would be greatly appreciated. Thank you.
    Attached Files Attached Files

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

    Re: Why is this VLOOKUP returning N/A?

    You have many spaces after the entries in column B and in column A of Sheet1. You should clean them up.

    Select column B of current sheet, hit CTRL+H and replace a space (enter a space in Find What)... with null (leave Replace With as is)... hit Replace All.

    Repeate with column A of Sheet1.

    Now the first still returns #N/A because no match exists.....

    you can use =IF(ISNA(VLOOKUP(B3,Sheet1!$A:$C,3,FALSE)),"",VLOOKUP(B3,Sheet1!$A:$C,3,FALSE)) to hide errors
    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.

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Why is this VLOOKUP returning N/A?

    Your security identifiers on Sheet1 are padded to the right with spaces.
    The security identifiers on the Original Report sheet are NOT.
    Consequently, there is no match.

    You could try this regular formula and copy it down:
    E3: =VLOOKUP(B3&"*",Sheet1!$A:$C,3,FALSE)

    OR...Find/Replace all of the data area spaces in Col_A on Sheet1 and Col_B on the Original Report sheet.

    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    02-28-2012
    Location
    Milwaukee
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Why is this VLOOKUP returning N/A?

    Hey guys,

    Thank you for the help. I have replaced the excess spaces and am using the =VLOOKUP(B3&"*",Sheet1!$A:$C,3,FALSE) formula that Ron had suggested.

    I included the file with the suggested changes. It has more securities in it now to show the report I am working with. I know that each of the securities on one tab are also on the other - i.e., each one should have a match.

    Any additional help you can provide would be great. And thanks again for the effort so far!
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Why is this VLOOKUP returning N/A?

    brewcitybiz,

    You have some numbers stored as text, and/or with leading zeros in eg B6 in TW Audit Request Results tab: 001084102, which I suspect is part of the problem.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Why is this VLOOKUP returning N/A?

    This should solve most of the problems:
    • Change the formula to this: =VLOOKUP(B3,Sheet1!$A:$C,3,FALSE)
    • Copy that formula down as far as you need.
    • Select the Col_B data on the TW Audit Request Results sheet
    ...Data.Text_to_columns
    ...Click: Next
    ...Click: Next
    ...Check: Text
    ...Click: Finish

    • Repeat for the Col_A data on Sheet1

    Does that help?
    Last edited by Ron Coderre; 11-05-2012 at 03:55 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Why is this VLOOKUP returning N/A?

    Further to my post above, see attached - I've converted values in B3:B27 on TW Audit tab to number format, and have used the following formula:

    Please Login or Register  to view this content.
    dragged down, which seems to be returning what you want (in Column F) without jumping through too many hoops

    Hope this helps.
    Attached Files Attached Files
    Last edited by BB1972; 11-05-2012 at 03:58 PM.

  8. #8
    Registered User
    Join Date
    02-28-2012
    Location
    Milwaukee
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Why is this VLOOKUP returning N/A?

    Dear all -

    The solution I tried which seemed to work great is Ron's. I did not try any of the others but Ron's solution and the original VLOOKUP I used (which he provided) work great to solve this.

    Thank you very much to everyone who posted - it helped me a great deal and I appreciate you taking the time to review and attempt to solve my problem. Best wishes to all.

  9. #9
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Why is this VLOOKUP returning N/A?

    You're very gracious, brew Glad you got sorted, and thanks for the rep.

  10. #10
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Why is this VLOOKUP returning N/A?

    I'm glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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