Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
A large proportion of questions in Excel newsgroups and Q&A boards regard #N/A (the Not Available error) the look up worksheet functions return.
The quotes that follow are from the Help file (of Excel 2000).
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Remarks
If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.
LOOKUP(lookup_value,lookup_vector,result_vector)
Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.
Result_vector is a range that contains only one row or column. It must be the same size as lookup_vector.
If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.
LOOKUP(lookup_value,array)
If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Remarks
If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.
If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.
MATCH(lookup_value,lookup_array,match_type)
Remarks
MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.
MATCH does not distinguish between uppercase and lowercase letters when matching text values.
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters, asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.
Although kindred, I excluded INDEX and CHOOSE, because they don't return #N/A.
What follows is a quote from a reply of mine at microsoft.public.excel.worksheet.functions to a post entitled "Conditional formatting to hide #N/A results", along with a reply to the same post by Harlan Grove:
It involves a proposal to extend the syntax of the look up functions quoted above with an (extra) optional argument.
QUOTE
A richer syntax for lookup functions would allow us to escape testing what
these functions compute (return), so avoiding the "compute twice" trap we
often see.
My proposal is simple: Add an optional slot to the sysntax of these
functions.
For VLOOKUP this surgery would give:
=VLOOKUP(lookup-value,table-array,col-index-num,{range-lookup},{return-value-when-not-available})
{} means optional; the default value for the 5th argument should be #N/A (in
view of backward compatibilty).
Examples:
=VLOOKUP(x,Table,c,0) [ return #N/A by default upon failure ]
=VLOOKUP(x,Table,c,0,"") [ return blank upon failure ]
=VLOOKUP(x,Table,c,0,0) [ return 0 upon failure ]
=VLOOKUP(c,Table,c,1) [ return #N/A by default upon failure; although in
most [cases] avoidable by approriate structuring of Table ]
=VLOOKUP(c,Table,c,1,0) [ return 0 upon failure; although in most [cases]
avoidable by approriate structuring of Table ]
PS. This is a renewal of a thread I was involved with at
http://www.mrexcel.com/archive2/messages/13513.html
What do you think? Am I overlooking something that would stand such a change
in the way?
Aladin
"Harlan Grove"<HrlnGrv@aol.com> wrote in message
news:xOuG8.27486$D41.1032708@bgtnsc05-news.ops.worldnet.att.net...
> Peo Sjoblom<terre08@mvps.org> wrote...
> >One possible way would be to use a white font and the formula
> >
> >=iserror(a1)
> >
> >assuming you want to hide it in A1
> >
> >You can of course trap the error using if and isna in the formula as
well..
> >
> >=if(isna(formula),"",formula)
>
> But it involves evaluating formula twice, which can seriously slow down
> Excel when formula is long and/or complicated. There are times when VBA
> user-defined functions can actually speed up Excel.
>
> 'trap errvals and return specified value or "" instead - return v if
> 'it's not a marched errval
> 'args: v is the *scalar* value to check
> ' e is an optional list of additional args used as follows
> ' - if 1st arg after v isn't an errval, use it as the return value
> if v is a
> ' matched errval; otherwise, use "" as the return value
> ' - all remaining args are treated as errvals to match v against,
so
> if
> ' no remaining args, match all errvals
> 'note: nonerrval args after 2nd arg effectively ignored
> '
> Function errortrap(v As Variant, ParamArray e() As Variant) As Variant
> Dim i As Long, m As Long, n As Long, t As Variant
>
> errortrap = v
>
> If Not IsError(v) Then Exit Function 'return quickly when not errval
>
> n = UBound(e)
>
> If Not IsError(e(0)) Then
> m = 1
> t = e(0)
> Else
> m = 0
> t = ""
> End If
>
> If n< m Then 'no more args, so matches all errvals
> errortrap = t
> Exit Function
> End If
>
> For i = m To n 'check specified errvals
> If v = e(i) Then
> errortrap = t
> Exit Function 'can return now
> End If
> Next i
> End Function
>
>
> In this case, use as =errortrap(formula,#N/A) or
> =errortrap(formula,"",#N/A) (to be explicit). More generally, to pass
> #VALUE!, #NUM! and #DIV/0! errors but replace #N/A, #NULL!, #REF! and
#NAME?
> errors with, say, -1, use =errortrap(formula,-1,#N/A,#NULL!,#REF!,#NAME?)
.
>
> If formula is simple, this will likely slow Excel down. However, if
formula
> involves 6 levels of nested fucntion calls, this will likely speed Excel
up.
>
> >another way assuming
> >
> >=if(countif(d2:d100,b1)=0,"",vlookup(b1,d2:e100,2,0))
>
> Better than evaluating the VLOOKUP twice.
>
> >or
> >
> >=if(countif(d2:d100,b1)=0,"",index(e2:e100,match(b1,d2:d100,0)))
>
> No benefit in this case to using INDEX(.,MATCH(.,.,0)) vs
VLOOKUP(.,.,.,0).
>
UNQUOTE
As long as Microsoft has not taken up the above proposal, we should at least live by a set of reasonable rules shown in the figure below (I posted this figure a while ago at this board, but I can't give here the hyperlink, simply because I couldn't find it via the board's search facility, hence this re-post.)
Aladin
This message was edited by Aladin Akyurek on 2002-05-29 16:12
The quotes that follow are from the Help file (of Excel 2000).
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Remarks
If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.
LOOKUP(lookup_value,lookup_vector,result_vector)
Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.
Result_vector is a range that contains only one row or column. It must be the same size as lookup_vector.
If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.
LOOKUP(lookup_value,array)
If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Remarks
If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.
If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.
MATCH(lookup_value,lookup_array,match_type)
Remarks
MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.
MATCH does not distinguish between uppercase and lowercase letters when matching text values.
If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters, asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.
Although kindred, I excluded INDEX and CHOOSE, because they don't return #N/A.
What follows is a quote from a reply of mine at microsoft.public.excel.worksheet.functions to a post entitled "Conditional formatting to hide #N/A results", along with a reply to the same post by Harlan Grove:
It involves a proposal to extend the syntax of the look up functions quoted above with an (extra) optional argument.
QUOTE
A richer syntax for lookup functions would allow us to escape testing what
these functions compute (return), so avoiding the "compute twice" trap we
often see.
My proposal is simple: Add an optional slot to the sysntax of these
functions.
For VLOOKUP this surgery would give:
=VLOOKUP(lookup-value,table-array,col-index-num,{range-lookup},{return-value-when-not-available})
{} means optional; the default value for the 5th argument should be #N/A (in
view of backward compatibilty).
Examples:
=VLOOKUP(x,Table,c,0) [ return #N/A by default upon failure ]
=VLOOKUP(x,Table,c,0,"") [ return blank upon failure ]
=VLOOKUP(x,Table,c,0,0) [ return 0 upon failure ]
=VLOOKUP(c,Table,c,1) [ return #N/A by default upon failure; although in
most [cases] avoidable by approriate structuring of Table ]
=VLOOKUP(c,Table,c,1,0) [ return 0 upon failure; although in most [cases]
avoidable by approriate structuring of Table ]
PS. This is a renewal of a thread I was involved with at
http://www.mrexcel.com/archive2/messages/13513.html
What do you think? Am I overlooking something that would stand such a change
in the way?
Aladin
"Harlan Grove"<HrlnGrv@aol.com> wrote in message
news:xOuG8.27486$D41.1032708@bgtnsc05-news.ops.worldnet.att.net...
> Peo Sjoblom<terre08@mvps.org> wrote...
> >One possible way would be to use a white font and the formula
> >
> >=iserror(a1)
> >
> >assuming you want to hide it in A1
> >
> >You can of course trap the error using if and isna in the formula as
well..
> >
> >=if(isna(formula),"",formula)
>
> But it involves evaluating formula twice, which can seriously slow down
> Excel when formula is long and/or complicated. There are times when VBA
> user-defined functions can actually speed up Excel.
>
> 'trap errvals and return specified value or "" instead - return v if
> 'it's not a marched errval
> 'args: v is the *scalar* value to check
> ' e is an optional list of additional args used as follows
> ' - if 1st arg after v isn't an errval, use it as the return value
> if v is a
> ' matched errval; otherwise, use "" as the return value
> ' - all remaining args are treated as errvals to match v against,
so
> if
> ' no remaining args, match all errvals
> 'note: nonerrval args after 2nd arg effectively ignored
> '
> Function errortrap(v As Variant, ParamArray e() As Variant) As Variant
> Dim i As Long, m As Long, n As Long, t As Variant
>
> errortrap = v
>
> If Not IsError(v) Then Exit Function 'return quickly when not errval
>
> n = UBound(e)
>
> If Not IsError(e(0)) Then
> m = 1
> t = e(0)
> Else
> m = 0
> t = ""
> End If
>
> If n< m Then 'no more args, so matches all errvals
> errortrap = t
> Exit Function
> End If
>
> For i = m To n 'check specified errvals
> If v = e(i) Then
> errortrap = t
> Exit Function 'can return now
> End If
> Next i
> End Function
>
>
> In this case, use as =errortrap(formula,#N/A) or
> =errortrap(formula,"",#N/A) (to be explicit). More generally, to pass
> #VALUE!, #NUM! and #DIV/0! errors but replace #N/A, #NULL!, #REF! and
#NAME?
> errors with, say, -1, use =errortrap(formula,-1,#N/A,#NULL!,#REF!,#NAME?)
.
>
> If formula is simple, this will likely slow Excel down. However, if
formula
> involves 6 levels of nested fucntion calls, this will likely speed Excel
up.
>
> >another way assuming
> >
> >=if(countif(d2:d100,b1)=0,"",vlookup(b1,d2:e100,2,0))
>
> Better than evaluating the VLOOKUP twice.
>
> >or
> >
> >=if(countif(d2:d100,b1)=0,"",index(e2:e100,match(b1,d2:d100,0)))
>
> No benefit in this case to using INDEX(.,MATCH(.,.,0)) vs
VLOOKUP(.,.,.,0).
>
UNQUOTE
As long as Microsoft has not taken up the above proposal, we should at least live by a set of reasonable rules shown in the figure below (I posted this figure a while ago at this board, but I can't give here the hyperlink, simply because I couldn't find it via the board's search facility, hence this re-post.)
| VLOOKUP.xls | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | ID | Last Name | Salary | ||||||||||||
| 2 | 010008 | Smith | 46,223.00 | EXACT MATCH (0 or FALSE, both means the same thing to Excel) | |||||||||||
| 3 | 010002 | Miller | 70,000.00 | VLOOKUP(lookup-value,lookup-table,table-column-to-look-in,0) | |||||||||||
| 4 | 010007 | Young | 46,223.00 | What is better? | |||||||||||
| 5 | 010005 | Thomas | 44,662.00 | Look Up | Salary? | ||||||||||
| 6 | 010001 | Smith | 70,000.00 | 010002 | 70,000.00 | this | |||||||||
| 7 | 70,000.00 | this, if #N/A must be avoided, because efficient | |||||||||||||
| 8 | 70,000.00 | inefficient | |||||||||||||
| 9 | 70,000.00 | inefficient & bad practice | |||||||||||||
| 10 | Commission Table | 70,000.00 | inefficient & bad practice | ||||||||||||
| 11 | Sales | % | |||||||||||||
| 12 | 0 | 0.00% | APPROXIMATE MATCH (1 or TRUE or nothing, all means the same thing to Excel) | ||||||||||||
| 13 | 2500 | 0.20% | VLOOKUP(lookup-value,lookup-table,table-column-to-look-in) | ||||||||||||
| 14 | 5000 | 0.40% | What is better? | ||||||||||||
| 15 | 6000 | 0.60% | Look Up | Commission | |||||||||||
| 16 | 7000 | 0.80% | 7,400.88 | 0.80% | this | ||||||||||
| 17 | 8000 | 1.00% | 0.80% | this, if #N/A must be avoided, because efficient; seldom needed | |||||||||||
| 18 | 9000 | 1.20% | As above, no ISNA, ISERR, or ISERROR. | ||||||||||||
| 19 | |||||||||||||||
| 20 | SPECIAL NOTE: INDEX/MATCH is faster than VLOOKUP, in particular when the formula must be copied to a huge number of cells. | ||||||||||||||
... | |||||||||||||||
Aladin
This message was edited by Aladin Akyurek on 2002-05-29 16:12
