+ Reply to Thread
Results 1 to 7 of 7

How to look up a cell with that includes a hyphen (-)

  1. #1
    Registered User
    Join Date
    12-13-2007
    Posts
    30

    How to look up a cell with that includes a hyphen (-)

    Please see attached file. In my column L, I'm trying to find a formula that would reference my column J to the table in the data tab. My problem is the data in column J has a hyphen (-) in the number ex.. 047-258-1 and the next tab does not have a hyphen in between the number...ex 0472581. The correct results in cell L3 would be Walmart. How can I adjust my formula to accommodate this hyphen. Can this also be reflected in my pivot table as well?

    Thank you for your assistance.
    Attached Files Attached Files
    Last edited by excellicious; 12-03-2008 at 10:51 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    The Formula for L3 is this...copy it down:
    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 12-03-2008 at 10:03 PM. Reason: Corrected formula to make absolute range
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    12-13-2007
    Posts
    30
    I'm not sure why your formula works for some cells but not for all cells. Some returns an incorrect answer and I get #NA for cell L6 & L13.

    Thank you for your efforts.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    There are some formatting issues. You need to format all the codes on the DATA sheet in column "CLD_BA_TXT" as TEXT. And you need to sort it by that column.

    Now this simpler formula will work:
    Please Login or Register  to view this content.
    Sample attached.

    What is it you want done with the PivotTable? It's not clear.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-13-2007
    Posts
    30
    That works beautifully but could you tell me how it works considering the column J is formatted in number and column "CLD_BA_TXT" as text?

    Thank you

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Column J is formatted as Number, but those hyphens turn it into text. If you used Custom Formatting to do the job, and let Excel add the hyphens, then maybe.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    Dude, I think I know why you wanted to remove the Pivot Table. It doesn't update automatically. Here's the sheet again with a regular COUNTIF functioning in place of the PivotTable.

    The PivotTable is more powerful, by far, so it's your call whether you use this or not. Take a look.
    Attached Files Attached Files

+ 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