Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to reference a number without a hyphen (2003)

    In sheet 1, my data is listed like this 01-96-5, 85-17-1, 09-26-5. In sheet 2, the table that I reference to data looks like 01965, 85171, 09265 and the return value that I want is located in the next column. Could you assist? Thank you.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to reference a number without a hyphen (2003)

    If I am reading your question right you have 01-96-5, 85-17-1, 09-26-5 in a column (let us assume column A in sheet 1 starting at A1) you then have 01965, 85171, 09265 in a column (let us assume in column A in sheet 2 starting at A1.

    For this formula to work the dat in sheet 2 column A should be in A-Z order and in Sheet1!B1 use this formula and copy down:

    =VLOOKUP(LEFT(A3,2)&MID(A3,4,2)&RIGHT(A3,1),Sheet2 !$A$1:$B$3,2)

    Otherwise match/index may assist
    Jerry

  3. #3
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reference a number without a hyphen (2003)

    Yoo..hoo...it works. Kudos to you Jerry!

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to reference a number without a hyphen (20

    I think Jezza's formula missing a zero at the end of the formula

    =VLOOKUP(LEFT(A3,2)&MID(A3,4,2)&RIGHT(A3,1),Sheet2 !$A$1:$B$3,2,0)

    And, or try this alternative one……

    =VLOOKUP(SUBSTITUTE(A1,"-",""),Sheet2!A$1:B$3,2,0)

    Regards
    Bosco

  5. #5
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reference a number without a hyphen (20

    It appears that there is a little issue. Your formula works in my practice sheet but not my workbook because of the format. when I place the cursor in cell A3 of sheet 1, it shows me 1965. When I place the cursor on cell A3 of sheet 2, I get '01965. I did try to format both at text and number but I get the error message. Please advice. Thank you.

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to reference a number without a hyphen (20

    I thought that Column A of Sheet 1 looked like 01-96-5.
    I suggest that you post the workbook stripped down to only a small portion of Sheets 1 & 2. Then tell us what you expect to see as a result in a specific cell.
    Regards
    Don

  7. #7
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reference a number without a hyphen (20

    Thank you for your reply Don. I was able to correct my formula =VLOOKUP(TEXT(A1,"00000"),Sheet2!A$1:B$30,2,) since the hyphens and zero are not REAL. It appears that I can't differentiate between a value or a text. It's a USER ERROR issue. If the format is Custom, would that consider a value or text? Thank you.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How to reference a number without a hyphen (20

    The lookups work with the values in the cells not what is displayed. [As opposed to something like Datafiltering which uses the display and not the value).

    If you want to lookup on the display text, you can create an intermediate column which uses the TEXT function to convert the value into formatted text and then lookup in this column.

    Steve

  9. #9
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reference a number without a hyphen (20

    That was very informative. Thank you Steve!

Posting Permissions

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