Prev: Transpose link
Next: Count "," in a cell
From: burtlake on 3 Jun 2010 06:22
I am using VLOOKUP to determine addresses from a master list. However I have
found that if the Lookup Value is NOT in the Table Array, I will get
incorrect information.

For example if the Table Array has entries for Schmidt and Schultz, and I
attempt to lookup Schnell (which is not in the Table Array), I will get the
value for Schultz, which is incorrect.

How can this be avoided? Or, how can I get a message saying the Lookup
Value was not found - rather than getting incorrect results?
From: Jacob Skaria on 3 Jun 2010 06:22
You would have missed out the last argument of VLOOKUP() Range_lookup which
is a logical value that specifies whether you want VLOOKUP to find an exact
match or an approximate match. If TRUE or omitted, an exact or approximate
match is returned. If an exact match is not found, the next largest value
that is less than lookup_value is returned..

=VLOOKUP(A1,Sheet2!A:D,2,FALSE)

OR

=VLOOKUP(A1,Sheet2!A:D,2,0)

If no entry is found it returns NA# . To handle this use IF() ISNA()
combination as below...Replace vlookup_formula string with your actual formula

=IF(ISNA(vlookup_formula),"Not Found",vlookup_formula)


--
Jacob (MVP - Excel)


"burtlake" wrote:

> I am using VLOOKUP to determine addresses from a master list. However I have
> found that if the Lookup Value is NOT in the Table Array, I will get
> incorrect information.
>
> For example if the Table Array has entries for Schmidt and Schultz, and I
> attempt to lookup Schnell (which is not in the Table Array), I will get the
> value for Schultz, which is incorrect.
>
> How can this be avoided? Or, how can I get a message saying the Lookup
> Value was not found - rather than getting incorrect results?
From: Ashish Mathur on 3 Jun 2010 06:59
Hi,

Try this

=if(iserror(vlookup(A3,B4:100,2,0)),"",vlookup(A3,B4:100,2,0))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"burtlake" <burtlake(a)discussions.microsoft.com> wrote in message
news:CA158CBF-5D05-4AD0-8EAE-B9F08D0E8EC0(a)microsoft.com...
> I am using VLOOKUP to determine addresses from a master list. However I
> have
> found that if the Lookup Value is NOT in the Table Array, I will get
> incorrect information.
>
> For example if the Table Array has entries for Schmidt and Schultz, and I
> attempt to lookup Schnell (which is not in the Table Array), I will get
> the
> value for Schultz, which is incorrect.
>
> How can this be avoided? Or, how can I get a message saying the Lookup
> Value was not found - rather than getting incorrect results?

 | 
Pages: 1
Prev: Transpose link
Next: Count "," in a cell