MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Question about vlookup..

Posted by JuBoPuki on July 13, 2001 11:29 AM


above is my formula. the problem is when
there is no data entered, the vlookup cell
will show #N/A. How can it will show nothing
when there is no data entered. Thanks in advance

Posted by Aladin Akyurek on July 13, 2001 11:38 AM





Posted by Russell on July 13, 2001 12:46 PM

An easier way...

Without using Index and Match:


Posted by Aladin Akyurek on July 13, 2001 1:18 PM

Re: An easier way...

Hi Russell,

(1) I often come across wordings by help seekers that makes me think that they don't really understand what #N/A means when returned by VLOOKUP. When attempting to retrieve a value X for a and #N/A is returned, they often seem to think that X does not exist in the lookup table. I'm hoping that MATCH makes them aware of the fact that it's "a", not X, which not exist in the lookup table.
(2) They pick up another important lookup function, which can often be used to determine the value of the 3rd arg of VLOOKUP.
(3) You got the right version of the non-existence test. I've seen too often a catch-all test (i.e., =IF(ISERROR(VLOOKUP(...) for which I developed, I admit, an aversion.
(4) I believed that using MATCH would be faster than using VLOOKUP itself for testing against #N/A, but that seems to be an illusion. (Someone at this bord checked MATCH against VLOOKUP and said that it didn't matter, alas.)
(5) It looks nicer to me, which is probably the main reason why I suggest the MATCH version so often instead of the "classic" version.

BTW, there is no INDEX involved. :-)



Posted by Scott S on July 13, 2001 3:57 PM

Re: An easier way...

If his Range_lookup is set to true (1), then it would still return a value unless the lookup_value was less than the begining value on the table, or his table was not in ascending order. What would you use if his Range_Lookup was false (0)? The isnumber(match() formula that you listed would still return #n/a. I'm only asking because I normally use ifna, or iferror formulas as well.

Posted by Aladin Akyurek on July 14, 2001 12:10 AM



I understand your concern. However, MATCH and VLOOKUP do not behave differently under the conditions of use that I suggested.

Lets take the following as our lookup table in A1:B5.


In C1 enter: 0.3 [ a lookup value ]

In D1 enter: =MATCH(C1,A1:A5,0) [ 0 for FALSE ]
In D2 enter: =VLOOKUP(C1,A1:A5,1,0) [ If you wish, =VLOOKUP(C1,A1:B5,2,0); it won't matter. ]

In E1 enter: =MATCH(C1,A1:A5,1) [ 1 for TRUE ]
In E2 enter: =VLOOKUP(C1,A1:A5,1,1)

In F1 enter: =ISNUMBER(MATCH(C1,A1:A5,0))
In F2 enter: =ISNA(VLOOKUP(C1,A1:A5,1,0))

In G1 enter: =ISNUMBER(MATCH(C1,A1:A5,1))
In G2 enter: =ISNA(VLOOKUP(C1,A1:A5,1,1))

All pairs should evaluate to the same result. Change the lookup value in C1 anyway you want, the pairs should behave identically.

PS. I used also to use ISNA(VLOOKUP..., but grown fond of ISNUMBER(MATCH... for the existence test. As I said before, I do not favor ISERROR(VLOOKUP... for this purpose, because it would confound #N/A with other types of errors, thus mislead the formula/model designer.

BTW, I developed a proposal for Microsoft to change the syntax of VLOOKUP so that the user can specify what it should return when the lookup value is not available. A substantial part of questions about VLOOKUP, as you certainly know,runs as "How can I make it return "" or 0 or "Not Found" instead of #N/A".

Posted by Eric on July 16, 2001 7:02 AM

Thx for the excellent discussion!