VLOOKUP QUESTION no match

Hi Andonny

It sure is. Just nest it with an ISERROR Formula:

IF(ISERROR(VLOOKUP(E1,A$1:B$65000,"2","FALSE")),0,VLOOKUP(E1,A$1:B$65000,"2","FALSE")

Good luck

Dave

OzGrid Business Applications

Andonny, while it's possible to return a zero if the
VLOOKUP() function returns #N/A... it is a very, VERY
inefficient practice. In the case of your formula,
=VLOOKUP(E1,A$1:B$65000,2,FALSE), Excel must search
65,000 cells just to determine that the value in
E1 is (or isn't) present... THEN if it is... it examine
the SAME 65,000 cells to return a result.

It makes more sense to handle the #N/A within the
formulas that use the results of the VLOOKUP(). For
example, if you're concerned about the impact of the
#N/A on a SUM() function then consider using
=SUMIF(range,"<>#N/A").

Hmmm, I don't think I agree. The VLOOKUP will find the result of a Lookup from a table that consists of Entire Columns (65536 row) in the blink of an eye. In fact nearly all Excels built in formulas can easily handle this. It's only when you start using array formulas that you CANNOT use entire columns. Even using half an Entire column within an array would Sloooow down Excel, even to the point of unusable if you have may of them. Any of Excels Database formulas will always beat an array hands down.

=IF(ISNA(VLOOKUP("M",A:B,1,FALSE)),0,ISNA(VLOOKUP("M",A:B,1,FALSE)))

I really cannot see why this is bad practice. If you need an entire column then it's the best way.

IMHO

Dave

OzGrid Business Applications

Dave, either you blink very slowly or there isn't much data in column A. Why don't you enter even numbers (2,4,6,8...) in cells A1:A65000, and the series, 1 thru 65000 in column B. Then enter your formulation, =IF(ISNA(VLOOKUP(B1,A$1:B$65000,2,0)),0,VLOOKUP(B1,A$1:B$65000,2,0)) into cell C1 and copy down. Better get some eye wash to keep your eyes moist. Oh yeah, now filter and unfilter this list a couple of times. Look at all this overhead just for a 0 that's unnecessary!

BTW, if you compare your formulation against a
VLOOKUP() using the test case I described below
you'll discover that it takes about 4.5 times as
long for your formulation to recalculate than a
standard VLOOKUP(). That's the price you pay for
that unnecessary zero.

Mark, I think it is you who needs the eye wash, re-read my statement.You miss have my point completely and have taken the missed point to the extreme.

I was quite obviously talking in the context of a SINGLE VLOOKUP looking for a value within a Table that consists of Entire Columns. NOT an entire column of VLOOKUPs. Better take a double dose of that eye wash :O)
OzGrid Business Applications

Dave, I understood perfectly what you were recommending. Whether it's a single lookup or 65,000 lookups the fact remains -- your solution could take as much as 4.5 times longer than a simple VLOOKUP(). My original commentary was that your recommendation was inefficent. You didn't believe me. So I provided a test case where you could easily measure the recalc time of your recommended solution. Now you know!

Sorry Mark, your so called example refers to having 65536 rows of VLOOKUPs and has absolutely nothing to do with my original post. Let's put it down to a communication breakdown.

But out of curiosity and the fact I was refering to a SINGLE VLOOUP and your last comment: "Whether it's a single lookup or 65,000 lookups the fact remains -- your solution could take as much as 4.5 times longer than a simple VLOOKUP()"

I'm very interested in how you tested and timed this ?

Dave

OzGrid Business Applications