#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" wrote in message
news:xOuG8.27486$D41.1032708@bgtnsc05-news.ops.worldnet.att.net...
> Peo Sjoblom 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.)

Microsoft Excel - VLOOKUP.xls ___Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
F6F7F8F9F10F16F17 =
	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.
Sheet1

To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above image was automatically generated by [HtmlMaker 2.0] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Aladin


[ This Message was edited by: Aladin Akyurek on 2002-05-29 16:12 ]

[Mark W.]

Yet another approach...

Microsoft Excel - Book1 ___Running: xl2000 : OS = Windows NT4.0
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
E2F2G2E3F3G3E4F4G4E5F5G5E6F6G6E7 =
	A	B	C	D	E	F	G
1	Employee	Salary		Name	Salary	Hide	F:G
2	Barry	100000		Phillip	n/a	Mary	75000
3	Cherie	125000		Cherie	$125,000	Cherie	125000
4	Larry	80000		Sally	$50,000	Sally	50000
5	Mary	75000		Ally	n/a	#N/A	#N/A
6	Sally	50000		Larry	$80,000	Larry	80000
7				Average	$85,000
8
9					Format E:E as…
10					$#,##0;;;[Red]"n/a"
Sheet1

To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above image was automatically generated by [HtmlMaker 2.10] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


[ This Message was edited by: Mark W. on 2002-05-29 17:34 ]

[Yogi Anand]

Hi Aladin and Mark:

Aladin: Thanks for taking up an issue which comes up quite often, and you bring up good points about inefficiency of certain formulations (If I may, I also term these as wasting system resources). Also, your point of adding another argument to the LOOKUP type functions is an interesting one.

Hi Mark:
Yours is a very interesting approach for exact lookups -- innovative! You capture the looked up value from the table , and right in one and the same formula, one knows that the value to be looked up is not in the Table.

I have not studied it enough yet, but what would be the implications, where one is not looking for an exact match ... I believe for that one would have to revert back to the traditional formulation, such as:

=VLOOKUP($D10,$A$10:$B$15,2)

one will have to deal with the #N/A errors in another way

Any how, for the exact lookups, I find your approach as very interesting.

Regards!

[Mark W.]

On 2002-05-29 17:19, Yogi Anand wrote:
...what would be the implications, where one is not looking for an exact match ... I believe for that one would have to revert back to the traditional formulation, such as:

=VLOOKUP($D10,$A$10:$B$15,2)

one will have to deal with the #N/A errors in another way

Yogi, your question above helped me notice that I had failed to account for the only case where #N/A could be returned from an approximate match... when the lookup_value is less than the 1st entry in the table_array. It's now fixed in my example above.

[Yogi Anand]

Hi Mark:
Continuing on using the LOOKUP, for me a number of modifications became necessary ... and to have a composite approach involving, excact and non-exact lookup values, numeric, text, and mixed numeric and text lookup values, I am providing the two worksheet simulations:

Your example for Employee Salaries

Microsoft Excel - Book1
B15		=
	A	B	C	D	E	F	G
1	Employee	Salary
2	Barrie	125000		Phillip	:alert('=IF(AND(ISTEXT(D2),D2<>F2),"n/a",IF(D2>=MIN($A$2:$A$6),G2))')>n/a	:alert('=VLOOKUP($D2,$A$2:$B$6,{1,2})')>Mary	:alert('=VLOOKUP($D2,$A$2:$B$6,{1,2})')>75000
3	Cherie	100000		Cherie	:alert('=IF(AND(ISTEXT(D3),D3<>F3),"n/a",IF(D3>=MIN($A$2:$A$6),G3))')>100000	:alert('=VLOOKUP($D3,$A$2:$B$6,{1,2})')>Cherie	:alert('=VLOOKUP($D3,$A$2:$B$6,{1,2})')>100000
4	Larry	80000		Sally	:alert('=IF(AND(ISTEXT(D4),D4<>F4),"n/a",IF(D4>=MIN($A$2:$A$6),G4))')>50000	:alert('=VLOOKUP($D4,$A$2:$B$6,{1,2})')>Sally	:alert('=VLOOKUP($D4,$A$2:$B$6,{1,2})')>50000
5	Mary	75000		Ally	:alert('=IF(ISNA(F5),"n/a",IF(AND(ISNUMBER(D5),D5n/a	:alert('=VLOOKUP($D5,$A$2:$B$6,{1,2})')>#N/A	:alert('=VLOOKUP($D5,$A$2:$B$6,{1,2})')>#N/A
6	Sally	50000		Larry	:alert('=IF(AND(ISTEXT(D6),D6<>F6),"n/a",IF(D6>=MIN($A$2:$A$6),G6))')>80000	:alert('=VLOOKUP($D6,$A$2:$B$6,{1,2})')>Larry	:alert('=VLOOKUP($D6,$A$2:$B$6,{1,2})')>80000
7				Average	:alert('=AVERAGE(E2:E6)')>76666.67
8
Sheet13 (2)

Click on a hyperlinked cell to see the underlying formula

Another example for Shipping Rates

Microsoft Excel - Book1
A1		=	Qty
	A	B	C	D	E	F	G
1	Qty	ShipRate				can hide	can hide
2	0	0		4	:alert('=IF(AND(ISTEXT(D2),D2<>F2),"n/a",IF(D2>=MIN($A$2:$A$6),G2))')>0.44	:alert('=VLOOKUP($D2,$A$2:$B$6,{1,2})')>4	:alert('=VLOOKUP($D2,$A$2:$B$6,{1,2})')>0.44
3	1	0.38		0	:alert('=IF(AND(ISTEXT(D3),D3<>F3),"n/a",IF(D3>=MIN($A$2:$A$6),G3))')>0	:alert('=VLOOKUP($D3,$A$2:$B$6,{1,2})')>0	:alert('=VLOOKUP($D3,$A$2:$B$6,{1,2})')>0
4	4	0.44		7	:alert('=IF(AND(ISTEXT(D4),D4<>F4),"n/a",IF(D4>=MIN($A$2:$A$6),G4))')>0.44	:alert('=VLOOKUP($D4,$A$2:$B$6,{1,2})')>4	:alert('=VLOOKUP($D4,$A$2:$B$6,{1,2})')>0.44
5	8	0.5		8	:alert('=IF(ISNA(F5),"n/a",IF(AND(ISNUMBER(D5),D50.5	:alert('=VLOOKUP($D5,$A$2:$B$6,{1,2})')>8	:alert('=VLOOKUP($D5,$A$2:$B$6,{1,2})')>0.5
6	11	boss		11	:alert('=IF(AND(ISTEXT(D6),D6<>F6),"n/a",IF(D6>=MIN($A$2:$A$6),G6))')>boss	:alert('=VLOOKUP($D6,$A$2:$B$6,{1,2})')>11	:alert('=VLOOKUP($D6,$A$2:$B$6,{1,2})')>boss
7				Average	:alert('=AVERAGE(E2:E6)')>0.35
8
Sheet13a (2)

Click on a hyperlinked cell to see the underlying formula

Mark: I could not make your proposed Custom Formatting to work with all situations involving both numeric and text entries. So, I have used n/a from within the formula(s)

Regards!

Edit: The formulas don't showup on clicking the hyperlinks ... the formula used in cell E2is;

=IF(AND(ISTEXT(D2),D2<>F2),"n/a",IF(D2>=MIN($A$2:$A$6),G2))

[ This Message was edited by: Yogi Anand on 2002-05-29 19:33 ]

[ This Message was edited by: Yogi Anand on 2002-05-29 19:37 ]

[Chris Davison]

Aladin,

my immediate thought in respect to anything "overlooked" would be 'compatability'

However, this would seem to have been addressed with the "optional" arguement...

Ommitting the 5th arguement defaults to N/A, which would bring any future augmentation in line with current VLOOKUP versions, thus allowing "old" formulae to work on "newer" versions



Those would be my first thoughts.... I'll be sure to keep this in mind to see if anything else filters through, cerebrally

[One comment : why restrict an N/A error to return a blank or a zero : could we not ask it to return a 1, or a 2, or a 3, for example, or even the value of a designated cell ? (ie where z20=50) Again, this could be cutting out a potential duplicate interrogation via VLOOKUP...... edit : hmmmmm, although this might lull people into a false sense of security if their lookup values and data values are out-of-sync

[Chris Davison]

comment continued....

Allowing the user to return "something else" in the prescence of an N/A could be a double-edged sword ?

Whilst most experienced users appreciate what the various errors actually mean, could this be a soft-option for people who have LOOKUP values which aren't equal to their data ?

ie "Harry" and "_Harry"

their lookup won't work, but they can bypass this with a 5th arguement, not realising that the proper lookup does in fact exist, it's just that their data needs to be "cleaned up"


For example, I'd consider myself inbetween intermediate and expert, and faced with an N/A in a lookup, I know it's telling me something I ought to be exploring.....

A beginner, however, may not appreciate this and be tempted to just use a 5th arguement opt-out clause

Hope this makes sense

As I mentioned before, I'll keep this bubbling away to see if anything else crops up

Chris

[Aladin Akyurek]

my immediate thought in respect to anything "overlooked" would be 'compatability'

However, this would seem to have been addressed with the "optional" arguement...

That's right. That has been my objective. Hope Microsoft will notice that.

Ommitting the 5th arguement defaults to N/A, which would bring any future augmentation in line with current VLOOKUP versions, thus allowing "old" formulae to work on "newer" versions

Yep.

Those would be my first thoughts.... I'll be sure to keep this in mind to see if anything else filters through, cerebrally

[One comment : why restrict an N/A error to return a blank or a zero : could we not ask it to return a 1, or a 2, or a 3, for example,

Those were just examples of the frequently used/desired return values... So scalar values of any kind is OK.

or even the value of a designated cell ? (ie where z20=50) Again, this could be cutting out a potential duplicate interrogation via VLOOKUP...... edit : hmmmmm, although this might lull people into a false sense of security if their lookup values and data values are out-of-sync


Chris, this is a darn interesting thought... a cell ref to indicate the value to return in case of failure or even a formula... Thanks for this great addition.

I'm hoping that someone in the VBA camp would want to write up a UDF, temporarily named e.g. ELOOKUP (extended VLOOKUP) that behaves according to the proposed specs so we could experiment with it.

Aladin

[Mark W.]

I still say that this best approach is to "scrub" your lookup_value or fix the table_array. Anytime an exact match returns #N/A every record in the table has been examined -- for NOT. This is very inefficient, especially, when there are 1000's of rows in the table! At least approximate matches allow you to "bail out" once it's obvious that no match exists.

[Aladin Akyurek]

On 2002-05-30 15:03, Chris Davison wrote:
comment continued....

Allowing the user to return "something else" in the prescence of an N/A could be a double-edged sword ?

Whilst most experienced users appreciate what the various errors actually mean, could this be a soft-option for people who have LOOKUP values which aren't equal to their data ?

ie "Harry" and "_Harry"

their lookup won't work, but they can bypass this with a 5th arguement, not realising that the proper lookup does in fact exist, it's just that their data needs to be "cleaned up"


For example, I'd consider myself inbetween intermediate and expert, and faced with an N/A in a lookup, I know it's telling me something I ought to be exploring.....

A beginner, however, may not appreciate this and be tempted to just use a 5th arguement opt-out clause

Hope this makes sense

As I mentioned before, I'll keep this bubbling away to see if anything else crops up

Chris

Once the user learns how to avoid/suppress #N/A, we have in fact the same outcome in the current situation... ensuing from a disregard for the "data types". With the new VLOOKUP, the questions might get a different turn: "While the lookup value is there, I get my return value instead of the value that I expect: Why?" Often a question about data types of course. I'm afraid that will be always with us.

Aladin