how do i get exact matches in a vlookup fomrula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shark1966 - 30 Sep 2006 04:25 GMT

in my vlookup formula, I am finding values that shouldn't have values are
returning the value for the closest match.

Reply to this Message

Biff - 30 Sep 2006 05:11 GMT

Set the 4th argument to FALSE or 0:

=VLOOKUP(A1,B1:C10,2,FALSE)

=VLOOKUP(A1,B1:C10,2,0)

Biff

> in my vlookup formula, I am finding values that shouldn't have values are
> returning the value for the closest match.

Reply to this Message

shark1966 - 30 Sep 2006 14:13 GMT

That worked great! Thanks. I am now receiving #N/A where there are no values.
How can I force the N/A to 0???

> Set the 4th argument to FALSE or 0:
>
[quoted text clipped - 6 lines]
> > in my vlookup formula, I am finding values that shouldn't have values are
> > returning the value for the closest match.

Reply to this Message

Dave Peterson - 30 Sep 2006 14:20 GMT

=if(iserror(vlookup(...)),0,vlookup(...))

If you're using xl2007:
=iferror(vlookup(...),0)

> That worked great! Thanks. I am now receiving #N/A where there are no values.
> How can I force the N/A to 0???
[quoted text clipped - 9 lines]
> > > in my vlookup formula, I am finding values that shouldn't have values are
> > > returning the value for the closest match.

Signature

Dave Peterson

Reply to this Message