Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    San Francisco Bay Area, California, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup Problem (Excel 2000) (9.0.4402 SR-1)

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> Problem is doing a table lookup using match function. Lookup table is in another workbook. Lookup values are text formatted. Calculate automatic.
    Problem: match formula returns #N/A. Solution has been to make the lookup value the active cell, then press F2 (EDIT), followed by ENTER. Then the result appears. I've written a VBA procedure to read the value of the cell, then write the value back using the formula method.

    I've also tried the F2 -ENTER operation on any other cell (doesn't work); F9 RECALC doesn't work.

    I'm advising a friend who doesn't write VBA, so that's not an option for her.

    What is the underlying problem here and what is a non-VBA solution (the tables needing lookup formulas have thousands of entries).

    Thanks, Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Problem (Excel 2000) (9.0.4402 SR-1)

    Do you have data imported from another application? That is a common cause of this kind of behavior.

    Things to try:
    <UL><LI>Select a single column of cells to be "treated". Then select Data | Text to Columns, Delimited option, click Finish. Or
    <LI>Select an empty cell and copy it to the clipboard. Select the cells to be "treated", then select Edit | Paste Special..., Add option, click OK. Or
    <LI>Export the workbook to HTML, then import the HTML file into Excel.[/list]HTH

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Albuquerque, New Mexico, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Problem (Excel 2000) (9.0.4402 SR-1)

    I just read Hans reply. I truly appreciate learning this trick! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    I have users who occasionally download data from another datasource into Excel. Sometimes the numbers import as text. <img src=/S/yep.gif border=0 alt=yep width=15 height=15> OR I have users who unknowingly format numbers as text and don't know they did it until they've saved and tried to create a formula. <img src=/S/smash.gif border=0 alt=smash width=30 height=26> Obviously, if it's only a cell or two, it's no big deal to fix them manually. If many cells are incorrectly formatted as text, I've taken the QUICKEST route I KNEW. I would import the worksheet into an Access table, format the numbers are numeric and export the data back into Excel. Now I KNOW there's an easier way!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Just for grins <img src=/S/grin.gif border=0 alt=grin width=15 height=15> and to satisfy my curiosity, I decided to see what Excel HELP would provide. (I knew I had searched HELP before.) I typed in convert text to number , convert text to numeric , and a variety of other combinations. I even typed <font color=red> Text to columns </font color=red> . As expected, HELP was less than helpful. <img src=/S/bif.gif border=0 alt=bif width=70 height=28> HELP came up with the following topic titles: <font color=blue> Separate copied text data into columns </font color=blue> and <font color=blue> Separate text across cells </font color=blue> . I guess I'm just thick-headed for not realizing that these topic titles would provide help on converting text to numbers! <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    Thanks again! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  4. #4
    New Lounger
    Join Date
    Jan 2003
    Location
    San Francisco Bay Area, California, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Problem (Excel 2000) (9.0.4402 SR-1)

    Hans,
    No success so far with the three things. You are right, though. The data were exported from an accounting package as a csv file, then imported into Excel using the Text to Columns feature with comma delimiter. I've tried several variations on your suggestions also.

    I've had two successes, as described below.

    FYI, the lookup table column is formatted as "General", while the data are a mix of integers( ex. 12580) and text (a 5 digit number, hyphen, 3 digit number; ex. 12579-310).

    (1) Cells containing the hyphenated values return the lookup values without problem.

    (2) For the cells with integers only, add an apostrophe at the beginning of the cell with the lookup value (ex. '12580). This still requires manipulating every cell, so it's not an improvement over the F2-ENTER solution, but it may be a clue as to the root cause.

    That's it for now. Onward and upward. Andy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup Problem (Excel 2000) (9.0.4402 SR-1)

    From your description, your problem may be the reverse from what I thought - values that should be text are interpreted as numbers instead of vice versa (but my suggestions helped another Lounger, so they are not wasted).

    Could you perhaps create a small sample spreadsheet to illustrate the problem? Just leave the bare minimum necessary to show what goes wrong?

  6. #6
    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: Lookup Problem (Excel 2000) (9.0.4402 SR-1)

    If you want to convert the numbers so all are text,. put this formula in row 1 of a blank column, copy it down the rows and then paste-special values over the source cells and they will all be text:

    <pre>=IF(ISNUMBER(A1),TEXT(A1,"0"),A1)</pre>


    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
  •