exact VLookup
 

Need to create a vlookup, but if the vlaue is not found to bring back a word
rather than the nearest number.

eg looking up number 99 and if my name range data stops at 80 it brings back
the value next to the 80.

all the code I've done so far is =vlookup(A1,MyNamedRange,2)

looking up whatever is in A1, looking for this in my named range, then
brining back the value in column 2 of the named range.

I know there's a way, to bring back only the exact match eg if there is no
99 then say Not found or leave empty?

exact VLookup
 

=IF(ISNA(VLOOKUP(A1,MyNamedRange,2,FALSE)),"NotFou nd",VLOOKUP(A1,MyNamedRange,2,FALSE))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"gigglygover" wrote in message ...
| Need to create a vlookup, but if the vlaue is not found to bring back a word
| rather than the nearest number.
|
| eg looking up number 99 and if my name range data stops at 80 it brings back
| the value next to the 80.
|
| all the code I've done so far is =vlookup(A1,MyNamedRange,2)
|
| looking up whatever is in A1, looking for this in my named range, then
| brining back the value in column 2 of the named range.
|
| I know there's a way, to bring back only the exact match eg if there is no
| 99 then say Not found or leave empty?
|

exact VLookup
 

If I've understood correctly, try this

=VLOOKUP(A1,MyNamedRange,2,TRUE)

Mike

"gigglygover" wrote:

Need to create a vlookup, but if the vlaue is not found to bring back a word
rather than the nearest number.

eg looking up number 99 and if my name range data stops at 80 it brings back
the value next to the 80.

all the code I've done so far is =vlookup(A1,MyNamedRange,2)

looking up whatever is in A1, looking for this in my named range, then
brining back the value in column 2 of the named range.

I know there's a way, to bring back only the exact match eg if there is no
99 then say Not found or leave empty?

exact VLookup
 

The 4th argument in the =vlookup() function tells it to look for an exact
match. The default (missing this 4th argument) is not to have an exact match.

So try:
=vlookup(a1,mynamedrange,2,false)

To avoid the #n/a error when there isn't a match:
=if(isna(vlookup(...)),"Missing",vlookup(...))

If you're using xl2007, you could use:
=iferror(vlookup(...),"Missing")







gigglygover wrote:

Need to create a vlookup, but if the vlaue is not found to bring back a word
rather than the nearest number.

eg looking up number 99 and if my name range data stops at 80 it brings back
the value next to the 80.

all the code I've done so far is =vlookup(A1,MyNamedRange,2)

looking up whatever is in A1, looking for this in my named range, then
brining back the value in column 2 of the named range.

I know there's a way, to bring back only the exact match eg if there is no
99 then say Not found or leave empty?

--

Dave Peterson