# Inconsistent results with =LOOKUP?

1. ## Inconsistent results with =LOOKUP?

I am using Lookup across 2 worksheets.
Where lookup finds a match, the result vector seems to work fine (too many
rows to check them all). However, when no match is found, the result vector
either returns a '1' or (in one instance only) a #N/A error.
I would have expected the error rather than the '1' or am I missing something?

The formula in sheet "2005": =LOOKUP(\$B3,'2004'!B\$3:B\$9689,'2004'!P\$3:P\$9689)
Any ideas?
--
Mike Watkins

--
Mike Watkins  Register To Reply

2. ## RE: Inconsistent results with =LOOKUP?

Hi Mike,

The LOOKUP function does not usually return #N/A when there's no exact
match. Rather, it goes to the next smallest value in the lookup vector and
returns the corresponding result vector. The only time it returns #N/A is
when your lookup value is smaller than the smallest value in the lookup
vector. You might want to try using VLOOKUP instead, if you want to always
return #N/A when there's no exact match. Hope this helps.

-Simon

"watkincm" wrote:

> I am using Lookup across 2 worksheets.
> Where lookup finds a match, the result vector seems to work fine (too many
> rows to check them all). However, when no match is found, the result vector
> either returns a '1' or (in one instance only) a #N/A error.
> I would have expected the error rather than the '1' or am I missing something?
>
> The formula in sheet "2005": =LOOKUP(\$B3,'2004'!B\$3:B\$9689,'2004'!P\$3:P\$9689)
> Any ideas?
> --
> Mike Watkins
>
> --
> Mike Watkins  Register To Reply

3. ## RE: Inconsistent results with =LOOKUP?

Then LOOKUP was working correctly - that's exactly what it did... VLOOKUP works perfectly for what I need - thanks for your help

--
Mike Watkins

"SiC" wrote:

> Hi Mike,
>
> The LOOKUP function does not usually return #N/A when there's no exact
> match. Rather, it goes to the next smallest value in the lookup vector and
> returns the corresponding result vector. The only time it returns #N/A is
> when your lookup value is smaller than the smallest value in the lookup
> vector. You might want to try using VLOOKUP instead, if you want to always
> return #N/A when there's no exact match. Hope this helps.
>
> -Simon
>
> "watkincm" wrote:
>
> > I am using Lookup across 2 worksheets.
> > Where lookup finds a match, the result vector seems to work fine (too many
> > rows to check them all). However, when no match is found, the result vector
> > either returns a '1' or (in one instance only) a #N/A error.
> > I would have expected the error rather than the '1' or am I missing something?
> >
> > The formula in sheet "2005": =LOOKUP(\$B3,'2004'!B\$3:B\$9689,'2004'!P\$3:P\$9689)
> > Any ideas?
> > --
> > Mike Watkins
> >
> > --
> > Mike Watkins  Register To Reply