Lookup/Index/Match does not work with Numbers

  • Thread starter Thread starter Bill Y
  • Start date Start date
B

Bill Y

Hello,

I have a few master reference tables that I use to complete entries on
another worksheet that basically compiles all of the reference data for a
given set of key values.

One of my reference tables has codes that are either numeric, alphanumeric,
or alphabetic. Associated with each code are several other values that I
want to look up and report in the compiling spreadsheet.

Entering either the alphanumeric or the alphabetic results in the additional
values being references and entered properly. However, when I try to enter
any of the numeric codes, I get #N/A.

The cell format does not seem to be relevant as the problem occurs in the
same set of cells on the compiling worksheet. For example, I enter an
alphabetic code and it works. I delete it and enter a numeric code, and it
does not work. That is, even using the same set of cells

Despite this I have tried Text, Numeric, and General cell formats, and have
even preceded the numeric entries with the single quote, ', to denote a text
entry. All of this was to no avail.

I have Excel 2000, but the spreadsheet I am working on might originally have
been created in '97.

Does anyone know anything about this?

Bill
 
It sure sounds like your numbers are sometimes text and sometimes numbers.

Can you try one more time:

copy an empty cell.
select your range to lookup
Edit|paste special|check Add

Do this for both key columns--in the table and the key to the lookup.

Any chance you have extra spaces in one, but not the other?

If you type 123 in both areas (both cells formatted as General), does it work?
 
The cell format does not seem to be relevant as the problem occurs in the
same set of cells on the compiling worksheet. For example, I enter an
alphabetic code and it works. I delete it and enter a numeric code, and it
does not work. That is, even using the same set of cells
Click to expand...

I would not expect the cell format to have any this kind of affect, as it
merely defines what is displayed, and not what is stored.

Can you give some examples of the kinds of numeric codes that do not work?

How are the numeric codes being generated?

If they or the lookup codes are being generated by a formula; or if they are
greater than 15 digits; it may be that what you see is not what is really being
stored.


--ron
 
Back
Top