Question about vlookup..

MODIFY THE ABOVE FORMULA AS

=IF(ISNUMBER(MATCH(Z6,$A$10:$A$14,1)),VLOOKUP(Z6,$A$10:$C$14,3,1),"")

Aladin

===============

An easier way...

Without using Index and Match:

=IF(ISNA(VLOOKUP(Z6,$A$10:$C$14,3,1)),"",VLOOKUP(Z6,$A$10:$C$14,3,1))

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

Aladin

===========

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.

MATCH Vs VLOOKUP

Scott,

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.

{1,"a";2,"b";3,"c";4,"d";5,"e"}

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

Thx for the excellent discussion!

:)