Using Excel 2002;

using a formula; =VLOOKUP($K$2,Sheet1!$A:$G,2) on cell K3

irrespective of what value I input into K2, I get a result in cell K3
regardless of whether K2 is valid or not

is this a bug or am I missing something?

Hi,

Try this
=VLOOKUP($K$2,Sheet1!$A:$G,2,0)

The addition of (,0) on the end of your formula will only return a result if
there is an exact match in your lookup table.

Hope this helps,

Gav.
"Sulasno" wrote:

Using Excel 2002;

using a formula; =VLOOKUP($K$2,Sheet1!$A:$G,2) on cell K3

irrespective of what value I input into K2, I get a result in cell K3
regardless of whether K2 is valid or not

is this a bug or am I missing something?

You're missing the 4th argument to the function, which directs it to look
for an *exact* match.

=VLOOKUP($K$2,Sheet1!$A:$G,2,0)

Without that last argument, you'll need to have your lookup list sorted (not
necessary when looking for exact matches), ascending, and Vlookup would then
return the *closest* match that's not larger then the lookup value.

Without the 4th argument, *and not* having the list sorted, you can get all
sorts of inaccurate returns.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===



"Sulasno" wrote in message
...
Using Excel 2002;

using a formula; =VLOOKUP($K$2,Sheet1!$A:$G,2) on cell K3

irrespective of what value I input into K2, I get a result in cell K3
regardless of whether K2 is valid or not

is this a bug or am I missing something?

thanks
re read the help and realise my function lacks the 4th argument

Any difference is using "0" or "False"

tia


"Gav123" wrote in message
...
Hi,

Try this
=VLOOKUP($K$2,Sheet1!$A:$G,2,0)

The addition of (,0) on the end of your formula will only return a result
if
there is an exact match in your lookup table.

Hope this helps,

Gav.
"Sulasno" wrote:

Using Excel 2002;

using a formula; =VLOOKUP($K$2,Sheet1!$A:$G,2) on cell K3

irrespective of what value I input into K2, I get a result in cell K3
regardless of whether K2 is valid or not

is this a bug or am I missing something?

No, they are the same thing. "0" is just quicker to type..

Regards,

Gav.

"Sulasno" wrote:

thanks
re read the help and realise my function lacks the 4th argument

Any difference is using "0" or "False"

tia


"Gav123" wrote in message
...
Hi,

Try this
=VLOOKUP($K$2,Sheet1!$A:$G,2,0)

The addition of (,0) on the end of your formula will only return a result
if
there is an exact match in your lookup table.

Hope this helps,

Gav.
"Sulasno" wrote:

Using Excel 2002;

using a formula; =VLOOKUP($K$2,Sheet1!$A:$G,2) on cell K3

irrespective of what value I input into K2, I get a result in cell K3
regardless of whether K2 is valid or not

is this a bug or am I missing something?