#### Aladin Akyurek

##### MrExcel MVP

- Joined
- Feb 14, 2002

- Messages
- 85,209

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.

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