Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup - Not Exact Match | Excel Worksheet Functions | |||
Using Exact() with VLookup or Match | Excel Worksheet Functions | |||
Vlookup where text not exact | Excel Discussion (Misc queries) | |||
vlookup more than one exact match | Excel Worksheet Functions | |||
How to get an exact mactch with Vlookup | Excel Worksheet Functions |