#N/A -- The Ever Recurring Dreaded Problem

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
ABCDEFGHIJKLM
1IDLast NameSalary
2010008Smith46,223.00EXACT MATCH (0 or FALSE, both means the same thing to Excel)
3010002Miller70,000.00VLOOKUP(lookup-value,lookup-table,table-column-to-look-in,0)
4010007Young46,223.00What is better?
5010005Thomas44,662.00Look UpSalary?
6010001Smith70,000.0001000270,000.00this
770,000.00this, if #N/A must be avoided, because efficient
870,000.00inefficient
970,000.00inefficient & bad practice
10Commission Table70,000.00inefficient & bad practice
11Sales%
1200.00%APPROXIMATE MATCH (1 or TRUE or nothing, all means the same thing to Excel)
1325000.20%VLOOKUP(lookup-value,lookup-table,table-column-to-look-in)
1450000.40%What is better?
1560000.60%Look UpCommission
1670000.80%7,400.880.80%this
1780001.00%0.80%this, if #N/A must be avoided, because efficient; seldom needed
1890001.20%As above, no ISNA, ISERR, or ISERROR.
19
20SPECIAL 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

Active Member
Joined
Apr 14, 2002
Messages
384
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
ABCDEFGHIJKLM
1IDLast NameSalary
2010008Smith46,223.00EXACT MATCH (0 or FALSE, both means the same thing to Excel)
3010002Miller70,000.00VLOOKUP(lookup-value,lookup-table,table-column-to-look-in,0)
4010007Young46,223.00What is better?
5010005Thomas44,662.00Look UpSalary?
6010001Smith70,000.0001000270,000.00this
770,000.00this, if #N/A must be avoided, because efficient
870,000.00inefficient
970,000.00inefficient & bad practice
10Commission Table70,000.00inefficient & bad practice
11Sales%
1200.00%APPROXIMATE MATCH (1 or TRUE or nothing, all means the same thing to Excel)
1325000.20%VLOOKUP(lookup-value,lookup-table,table-column-to-look-in)
1450000.40%What is better?
1560000.60%Look UpCommission
1670000.80%7,400.880.80%this
1780001.00%0.80%this, if #N/A must be avoided, because efficient; seldom needed
1890001.20%As above, no ISNA, ISERR, or ISERROR.
19
20SPECIAL 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.)

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,255
Messages
5,769,067
Members
425,515
Latest member
baltusf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top