vlookup error

[mssbonnie]

I am trying to enter vlookup and it is returning a value of #NA. Both of my
spreadsheets are sorted in ascending order and both have values in the
approprate columns. This is how it is entered
"=VLOOKUP(A3,Affidavit!A1:Y13771,19,FALSE)" Can anyone find the error?

 

[Ken Wright]

Is the value you are looking up actually in that column?
Even if you think it is, try using =A3=CELL_THAT_YOU_THINK_MATCHES and see
if it returns TRUE
1234 does not necessarily equal 1234 - One could be a number and one could
be text etc etc

Regards
Ken.......................

 

[mssbonnie]

The column does contain a number. I tried to retype the reference number and
it works. I have formatted the column as text numerous times but I seem to
have to re-enter the reference numbers one at a time. That is a problem
since there are 40,000 lines. Do you know of any way to do this
automatically since formatting as text is not working?

 

[Ken Wright]

LOL - frustrating isn't it, but I was pretty sure that that would be the
reason.

Anyway, the easy fix is as follows:

Format the column of data as General, or number or whatever numeric you
want.
Now in an empty cell somewhere, type a 1
Copy that cell
Select your range of data and then do Edit / Paste Special / Tick values and
Multiply
Hit Ok and job done.

Regards
Ken.......................

 

[mssbonnie]

It is not working, could it be because it is an alpha-numeric?

 

[Ken Wright]

My apologies - would help if I read the question correctly :-( I thought
you needed it all numeric.

Are all your entries alpha-numeric, only surely they are already text if
they are?

You can always get round it by forcing the formula to read it as text, eg

=VLOOKUP(A1,TEXT($B$1:$B$100,"@"),2,0) array entered using CTRL+SHIFT+ENTER

Doesn't help if you have missing leading zeroes though, unless of course all
your ref numbers are identical in length, and as such can be deduced from
your numeric entries.

Still always prefer to fix the data source though, so assuming your data is
in A1:A40000
in say B1, put =""&A1 and hit enter. Now double click the little black
cross hairs bottom right of the cell itself and that formula should run all
the way to the bottom

Now format A1:A40000 as text
Now copy B1:B40000, then select A1 and do edit / Paste Special / Values

Regards
Ken........................

 

[mssbonnie]

Thank You Thank You! Some of my numbers and text references are different
lengths, since I have only 4 repeatedtext references I assigned them a
numeric value and it all worked. Couldn't have done it without you. Many
thanks again.

 

[Ken Wright]

You're very welcome indeed - Glad to have been of help

Best Wishes
Ken........................

Thank You Thank You! Some of my numbers and text references are different
lengths, since I have only 4 repeatedtext references I assigned them a
numeric value and it all worked. Couldn't have done it without you. Many
thanks again.

<snip>