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

Topic

New Reply

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

Reply | Quote

Replies

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

Things to try:

• Select a single column of cells to be “treated”. Then select Data | Text to Columns, Delimited option, click Finish. Or
• 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
• Export the workbook to HTML, then import the HTML file into Excel.
  [/list]HTH

Reply | Quote

I just read Hans reply. I truly appreciate learning this trick!

I have users who occasionally download data from another datasource into Excel. Sometimes the numbers import as text. 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. 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!!

Just for grins 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 Text to columns . As expected, HELP was less than helpful. HELP came up with the following topic titles: Separate copied text data into columns and Separate text across cells . I guess I’m just thick-headed for not realizing that these topic titles would provide help on converting text to numbers!

Thanks again!

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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:

=IF(ISNUMBER(A1),TEXT(A1,"0"),A1)

Steve

Reply | Quote