Re: VLOOKUP #NA Unless retyped

[hgrove]

Bob L. wrote...

Hello, I am using VLOOKUP with 9000 rows and 8 columns. I
have tried reformating the cells to general, and text and have
had no luck.

Formatting has no effect on cell values, so is always ineffective a
solving this sort of problem.

If I retype the cells the lookup works great, but I don't want to
retype all 9000 cells.

...

What's in the first column of the Tprice range? If it's supposed t
contain nothing but numbers, then you could copy any *blank* cell
select the first column of Tprice, Edit > Paste Special, select Value
in the Paste section and Add in the Operation section of the Past
Special dialog and click OK. Alternatively, change your lookup formul
to

=VLOOKUP(A15&"",Tprice,2,FALSE

 

[Guest]

Thanks for the reply, but the first column contains text and numbers. I have tried formating as both general and text. Still no luck

 

[hgrove]

Bob L wrote...

Thanks for the reply, but the first column contains text and
numbers. I have tried formating as both general and text. Still
no luck

...

How many times will you need to be told FORMATTING *DOES* *NOT
*WORK*!! Don't keep telling us formatting doesn't work because most o
us KNOW it doesn't work.

Reread the bottom portion of my last response.

:
...

. . . Alternatively, change your lookup formula to

=VLOOKUP(A15&"",Tprice,2,FALSE)

Do you get #N/A results when A15 is both text and numeric or only whe
A15 is numeric? If only when numeric, then you might need to use th
following *array* formula instead.

=INDEX(Tprice,MATCH(A15&"",TRIM(INDEX(Tprice,0,1)),0),2

 

[Myrna Larson]

Harlan's suggestion re copying a blank cell will not bomb on text entries. But
it WILL ensure that all entries that are supposed to be numeric have in fact
been stored by Excel as numbers rather than text.

If you have a mix, and the 9000 rows are presumably not sorted, are you
remembering to use a 0 as the 4th argument of VLOOKUP? That requires an exact
match, and should handle the mixed data OK.

Thanks for the reply, but the first column contains text and numbers. I have

tried formating as both general and text. Still no luck

 

[Myrna Larson]

The problem is most likely that the number you are looking for is a number,
and the corresponding value in the table is text. Again, you MUST convert
everthing to numbers or to text.

Myrna,

Thanks for your reply.
I do have a mix and I have sorted them ascending order.
I have the problem when setting the fourth argument to false or 0. It works

when I leave this part off, however I need the exact referance not the
closest.

Any ideas?

Bob

 

[Ghly]

Since you have not indicated what type of data you have or want, I can
suggest these:

If you want to convert numbers that are entered as text into numbers,
you can:
1) copy that data that is text, then in Excel 2003, after pasting,
select "Values and Number Formating" from the little clipboard that appears.
2) or do as the Help suggests - put a 1 in a cell, copy it, select the
cells you want to format as numbers, paste special, select "Multiply"

When using VLOOKUP, the data in the first column should match the type
of data you are searching for. If you have VLOOKUP(1,A1:B10,2,FALSE),
then the column A1:A10 should be numbers. Also note the 2 above
indicates the return value will come from the range B1:B10, and will
return 0 if the cell is blank.

The data you are looking for (1 in the above example) must exist or
VLOOKUP will return #N/A. IF you don't like the #N/A, you can do
something like =IF(ISNA(MATCH(D6,$B$1:$B$10,0)),"",MATCH(D6,$B$1:$B$10,0))
or
=IF(ISNA(VLOOKUP(D5,$B$1:$B$10,1,FALSE)),"",VLOOKUP(D5,$B$1:$B$10,1,FALSE))
which tests to see if #N/A is returned, and if so (result is TRUE), then
will show a blank in that cell, otherwise (result is FALSE) will do the
VLOOKUP or MATCH.

Dy