VLookup

[jvillar3]

I'm trying to link two worksheets using the vlookup function, but I'm receiving several #value errors. I've tested the link and vlookup function using other identical worksheets, and everything is OK.

[Vito]

Are the formats for your lookup value and your lookup table the same? They should be for Vlookup to work properly.

[jvillar3]

The formatting is the same for the both spreadsheets. I've also checked for any spaces or returns. thanks.

[Vito]

Can you post your formula? And perhaps some sample data from your tables and your lookup values?

[Niek Otten]

Use ISNUMBER() or ISTEXT for both series to make sure there is no text which
looks like numbers but isn't. Formatting will not help.
If there is text which should be numbers, do the following:

Format an empty cell as General. Enter the number 1.
Edit>Copy
Select your "numbers"
Edit>Paste special, check Multiply

Why not post your formula? What is the 4th argument? Is the data sorted?

--
Kind regards,

Niek Otten

"jvillar3" <[email protected]> wrote in
message news:[email protected]...
>
> The formatting is the same for the both spreadsheets. I've also checked
> for any spaces or returns. thanks.
>
>
> --
> jvillar3
> ------------------------------------------------------------------------
> jvillar3's Profile:
> http://www.excelforum.com/member.php...o&userid=29212
> View this thread: http://www.excelforum.com/showthread...hreadid=489400
>

[jvillar3]

my formula is "=-VLOOKUP($O215,RANGE,2,FALSE)" where $O is a number. I've verified there is no text, only number. The other thing I'll mention is that while one row of information, the following row of information will have #value errors, while the formulas are the same. I'm sorry but I can provide the data.

[Dave Peterson]

If you remove the negative symbol, what does your formula return?

=VLOOKUP($O215,RANGE,2,FALSE)

If it returns a string of text (like: ASDF), then -ASDF would cause #value to
occur.




jvillar3 wrote:
>
> my formula is "=-VLOOKUP($O215,RANGE,2,FALSE)" where $O is a number.
> I've verified there is no text, only number. The other thing I'll
> mention is that while one row of information, the following row of
> information will have #value errors, while the formulas are the same.
> I'm sorry but I can provide the data.
>
> --
> jvillar3
> ------------------------------------------------------------------------
> jvillar3's Profile: http://www.excelforum.com/member.php...o&userid=29212
> View this thread: http://www.excelforum.com/showthread...hreadid=489400

--

Dave Peterson

[Niek Otten]

Did you test the numbers using ISNUMBER?

--
Kind regards,

Niek Otten

"jvillar3" <[email protected]> wrote in
message news:[email protected]...
>
> my formula is "=-VLOOKUP($O215,RANGE,2,FALSE)" where $O is a number.
> I've verified there is no text, only number. The other thing I'll
> mention is that while one row of information, the following row of
> information will have #value errors, while the formulas are the same.
> I'm sorry but I can provide the data.
>
>
> --
> jvillar3
> ------------------------------------------------------------------------
> jvillar3's Profile:
> http://www.excelforum.com/member.php...o&userid=29212
> View this thread: http://www.excelforum.com/showthread...hreadid=489400
>

[Niek Otten]

Both ranges, the ones you use to lookup and the ones you lookup in?
I'm quite sure that is the problem.

--
Kind regards,

Niek Otten

"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> Did you test the numbers using ISNUMBER?
>
> --
> Kind regards,
>
> Niek Otten
>
> "jvillar3" <[email protected]> wrote in
> message news:[email protected]...
>>
>> my formula is "=-VLOOKUP($O215,RANGE,2,FALSE)" where $O is a number.
>> I've verified there is no text, only number. The other thing I'll
>> mention is that while one row of information, the following row of
>> information will have #value errors, while the formulas are the same.
>> I'm sorry but I can provide the data.
>>
>>
>> --
>> jvillar3
>> ------------------------------------------------------------------------
>> jvillar3's Profile:
>> http://www.excelforum.com/member.php...o&userid=29212
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=489400
>>
>
>

[jvillar3]

I found my error. The range that I was using for the table_array did not cover all the data (over 2000 rows). I simply expanded my range and it took care of everything. Thanks to all

[Niek Otten]

Congratulations!

Don't hesitate to post again if you have an Excel problem, but please try to
be a bit more informative. We more or less had to drag the information out
of you.

--
Kind regards,

Niek Otten

"jvillar3" <[email protected]> wrote in
message news:[email protected]...
>
> I found my error. The range that I was using for the table_array did
> not cover all the data (over 2000 rows). I simply expanded my range
> and it took care of everything. Thanks to all
>
>
> --
> jvillar3
> ------------------------------------------------------------------------
> jvillar3's Profile:
> http://www.excelforum.com/member.php...o&userid=29212
> View this thread: http://www.excelforum.com/showthread...hreadid=489400
>