Results 1 to 9 of 9

VLOOKUP not working with Column of Numbers

This is a discussion on VLOOKUP not working with Column of Numbers within the Excel Questions forums, part of the Question Forums category; I am pulling my hair out trying to get a simple VLOOKUP function to work. I have a sheet that ...

  1. #1
    Board Regular
    Join Date
    May 2004
    Posts
    307

    Default VLOOKUP not working with Column of Numbers

    I am pulling my hair out trying to get a simple VLOOKUP function to work. I have a sheet that was copy/pasted from an MS-Access query.

    I think something is very wrong. The cells in the look-up sheet "Reference" are numbers, but the Format is General - their values are "261", "1417", "10055", etc.

    My cell A1 has the value "10055" and has Format of General. But
    =A1='Reference'!A5 comes back FALSE, which is not at all what I expected. However,
    =A1='Reference'!A5+0 comes back TRUE. So I'm very confused

    My ultimate goal is of course to get
    = VLOOKUP(A1,'Reference'!A:C,3,FALSE)
    to work, so I can retrieve from Col C what matches from Col A. Can I put "+0" anywhere in the 'Reference' side of the equation to convert the look-up column to numerics? I really didn't think I'd hav eto do that!

    Of course, VLOOKUP works when Character Strings are in the look-up Column, but these "numbers" are causing havoc.

    Ultimately, instead of A1, I will be look up via the sheet name, which will be a number, like 10055, so I really need
    =VLOOKUP(RIGHT(CELL("FILENAME",A1),LEN(CELL("FILENAME",A1))-FIND("]",CELL("FILENAME",A1))), 'Reference'!A:C, 3, FALSE) to work.

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,887

    Default Re: VLOOKUP not working with Column of Numbers

    If you are copying and pasting from an Access query, it will usually paste everything as text, evem numbers (at least mine does).

    Highlight the column of numbers, change the format to General, then select "Text to Columns" from the Data menu and click finish. This should convert all those numbers in Text format to Numeric format.

    Then the VLOOKUP should work fine.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    May 2004
    Posts
    307

    Default Re: VLOOKUP not working with Column of Numbers

    Thanks - I'm getting close. I did the Data->Text to Columns tip and now this returns TRUE:
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0='Reference'!A2

    which takes my Sheet Name, then converts to numeric via "+0".

    Unfortunately, this returns "VLOOKUP=":
    ="VLOOKUP=" & VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0,'Reference'!A:C,3,FALSE)

    not sure why the VLOOKUP would not return the matching lookup.

  4. #4
    Board Regular
    Join Date
    May 2004
    Posts
    307

    Default

    Actually, I got it to work using the trusty INDEX/MATCH combo:

    =INDEX('Reference'!C:C,MATCH(1,('Scope Reference'!$A$1:$A$9999=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0)*(1=1),0))

    for some Reason INDEX/MATCH never lets me down with Numeric lookups, wheareas VLOOKUP can cause aggravation. Plus, MATCH is more forgiving in case someday the lookup column is not sorted.

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,933

    Default

    Quote Originally Posted by pbassett
    Actually, I got it to work using the trusty INDEX/MATCH combo:

    =INDEX('Reference'!C:C,MATCH(1,('Scope Reference'!$A$1:$A$9999=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0)*(1=1),0))

    for some Reason INDEX/MATCH never lets me down with Numeric lookups, wheareas VLOOKUP can cause aggravation. Plus, MATCH is more forgiving in case someday the lookup column is not sorted.
    Strange formula...

    What is a typical result of..

    MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) ?

  6. #6
    Board Regular
    Join Date
    May 2004
    Posts
    307

    Default Re: VLOOKUP not working with Column of Numbers

    It returns the sheet name, like "10055" without the filename, pathname, etc. I need just the sheet name, since I use this as a unique identifier for looking up data from Master Sheets. Actually, that brings me to my next question.

    I would like to write a custom formula MySheetName to save some space in these cell formulas. This is my first attempt at writing a function, but unfortunately it returns #VALUE

    ====
    Function ThisSheetName() As String
    ThisSheetName = Application.WorksheetFunction.Mid(Application.WorksheetFunction.CELL("filename", A1), Application.WorksheetFunction.Find("]", Application.WorksheetFunction.CELL("filename", A1)) + 1, 255)
    End Function
    ====

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,933

    Default Re: VLOOKUP not working with Column of Numbers

    Quote Originally Posted by pbassett
    It returns the sheet name, like "10055" without the filename, pathname, etc. I need just the sheet name, since I use this as a unique identifier for looking up data from Master Sheets...
    If that's case...

    Change:

    =INDEX('Reference'!C:C,MATCH(1,('Scope Reference'!$A$1:$A$9999=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0)*(1=1),0))

    to:

    =INDEX('Reference'!C:C,MATCH(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0,'Scope Reference'!$A:$A,0))

    BTW, are 'Reference' and 'Scope Reference' different sheets? If not, you'll need to align them.

  8. #8
    Board Regular
    Join Date
    May 2004
    Posts
    307

    Default Re: VLOOKUP not working with Column of Numbers

    Thanks! I tried to simplify my question by changing 'Scope Reference' to 'Reference', but i didn't fix allreferences!

    I thought it was lame to add the (1=1) in my MATCH but that's because the first argument was 1.

    If you can help 1 more time, do you have any tips on the Function? I'm sure it's easy.

    Thanks again,
    Pete

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,933

    Default Re: VLOOKUP not working with Column of Numbers

    Quote Originally Posted by pbassett
    Thanks! I tried to simplify my question by changing 'Scope Reference' to 'Reference', but i didn't fix allreferences!

    I thought it was lame to add the (1=1) in my MATCH but that's because the first argument was 1.
    MID(...)+0 effects the necessary coercion from text-number to real number, so the *(1=1) bit superfluous.

    If you can help 1 more time, do you have any tips on the Function? I'm sure it's easy...
    Pete, the latter requires a VBA programmer.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com