VLOOKUP

[Sulasno]

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?

 

[Gav123]

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.

 

[RagDyeR]

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!
=====================================================



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?

 

[Sulasno]

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

Any difference is using "0" or "False"

tia

 

[Gav123]

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

Regards,

Gav.