Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    vlookup (xp or 2003)

    I tried all sort of ways for this vlookup to work on the CUMB tab. The results (formula) is in column H and it's looking into worksheet PROVIDER from columns A-C recording the item in C from the match found in Column A.

    Any suggestions.

    Martin

  2. #2
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (xp or 2003)

    I applied the range name MyTable to Provider!$A$5:$C$1179.

    Then I used the following formula:

    =VLOOKUP(TEXT(TRIM(B2),"000000"),MyTable,3,0)

    It seemed to work. For non-matches (#N/A), you might want to alter the formula a bit.

  3. #3
    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: vlookup (xp or 2003)

    Your cells are formatted as "TEXT".

    Format H2 as "general" [Format - cells - Number(tab) - Category ] and then copy/autofill this down the column

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: vlookup (xp or 2003)

    Is there any way of including the formating in a formula? So the formula does this work and becomes invisible to the user.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (xp or 2003)

    No, formulas can not control formatting.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup (xp or 2003)

    I agree that formulas cannot apply formatting in the sense of font attributes (bold, underline, italics, size).

    Part of my earlier suggestion involved implementing the TEXT function so that the value in B2 would match the "format" of the first column in his lookup table which included leading zeros. I am curious if the poster has tried this suggestion.

  7. #7
    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: vlookup (xp or 2003)

    I don't understand what you are after exactly. Doesn't the formula do what you want? If not you will have to explain what you need.

    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
  •