#N/A -- The Ever Recurring Dreaded Problem

[Aladin Akyurek]

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

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

 

[s-o-s]

On 2002-05-29 15:07, Aladin Akyurek wrote:
..... Lots of deleted lines....

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

In this table the next to last line suggests that Vlookup is slower than Index / Match.

This is something that I have for a long time believed and this thread has caused me to investigate... to this end I wrote a small macro for both to test 10,000 lines of lookups/index formulas and to record how long each scenario takes... answer I find rather surprising as it counter acts our common held view!!!

Vlookup took approx 2 seconds less time to resolve?

If you want to repeat exercise...I randomly entered 10000 lines using a rand function to populate with the letters A-J, then copied and pasted values.

Next in range G1:G10 was letters A-J

In H1:H10 was values 100-1000 incrementing in 100's.

Next I used the macros below which then populate the column B with the relevant formulas and finally put the time in Cells C3 for Lookup and C7 for Index/Match. (This happened to align them against the macro buttons on my SS.)

Sub TestVlook()
Range("B2:B10001").ClearContents
Application.ScreenUpdating = False
StrtTim = Timer

For a = 2 To 10001
    ca = "A" & a
    cb = "B" & a
    Range([cb]).Formula = "=vlookup(" & ca & ",$G:$H,2,false)"
Next

EndTim = Timer
TotTime = (EndTim - StrtTim)
Range("C3").Value = TotTime
Application.ScreenUpdating = True
End Sub


Sub TestIndex()
Range("B2:B10001").ClearContents
Application.ScreenUpdating = False
StrtTim = Timer

For a = 2 To 10001
    ca = "A" & a
    cb = "B" & a
    Range([cb]).Formula = "=index($G:$H,match(" & ca & ",$G:$G,0),2)"
Next

EndTim = Timer
TotTime = (EndTim - StrtTim)
Range("C7").Value = TotTime
Application.ScreenUpdating = True
End Sub

Comments???

 

[Tazguy37]

Shouldn't the above code be changed to time the calculation time rather than the time it takes to enter the formulas in each cell and calculate them?