exact VLookup

[gigglygover]

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?

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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?
|

 

[Mike H]

If I've understood correctly, try this

=VLOOKUP(A1,MyNamedRange,2,TRUE)

Mike

 

[Dave Peterson]

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")