VLOOKUP FORMULA
Posted by Hal Turchin on January 14, 2002 5:03 AM
I'm using the following formula in a worksheet:
=IF(VLOOKUP($A32,OpenPO,1)=$A32,VLOOKUP($A32,OpenPO,3),0)
If the Item exists I get the result. If the item in the table does not exist I get N/A. I want to get a zero(0) if the item does not exist. Can someone please rewrite the formula for me to get the zero.
Thanks
Posted by Aladin Akyurek on January 14, 2002 5:45 AM
Hal --
It's:
either
=IF(COUNTIF(x,$A32),VLOOKUP($A32,OpenPO,3,0),0)
or
=IF(ISNUMBER(MATCH($A3,x)),VLOOKUP($A3,OpenPO,3),0)
where I assume OpenPO to be a table of at least 3-columns. The "x" in above formula must be replaced with the first column of the table OpenPO. For example, if OpenPO is in the range E2:G200, then x is E2:E200. The first formula should be used, if an exact match needed, otherwise the second should be used.
Aladin
==========
Posted by Mark W. on January 14, 2002 8:06 AM
Hal, another approach would be to allow for
intermediate results in a couple of cells and
then use a separate IF function. Just use the
array formula, {=IF(VLOOKUP($A32,OpenPO,{1,3})},
to return both the left-most column of your table
as well as the column 3 value of interest. For
discussion purposes let's suppose that these
values are returned to cells B2:C2. Now you
can use =IF(ISNA(A32),0,IF(B2=A32,C2,0)) to
produce the desired results. If you're willing
to add -9.99999999999999E307 as the first entry
in your lookup table then the formula,
=IF(B2=A32,C2,0), could be used instead.
Posted by Mark W. on January 14, 2002 8:25 AM
IF formula correction!
Posted by Aladin Akyurek on January 14, 2002 8:41 AM
Re: IF formula correction!
Interesting proposal. But, how about the accruing costs that will occur if applied for 1000 or more retrievals?
Aladin
======
Posted by Mark W. on January 14, 2002 8:58 AM
Re: IF formula correction!
Ahh, the classic workbook size vs. lookup time
tradeoff. Certainly, one would have to consider
such implications for their application. Another
advantage of my recommendation is that given
a large, sorted lookup table one doesn't have to
use a VLOOKUP or MATCH with it's last, optional
argument set to FALSE to determine the absence
of a lookup value. Once the lookup value is
exceeded (perhaps, after examining only a few
entries) a result is returned, and then checked
by the IF function. This could be preferrable
to searching an entire 1000+ row table. Tuning
an application is art! Right?
Interesting proposal. But, how about the accruing costs that will occur if applied for 1000 or more retrievals?
Posted by Aladin Akyurek on January 14, 2002 9:07 AM
It's...
certainly worth applying.
Ahh, the classic workbook size vs. lookup time tradeoff. Certainly, one would have to consider such implications for their application. Another advantage of my recommendation is that given a large, sorted lookup table one doesn't have to use a VLOOKUP or MATCH with it's last, optional argument set to FALSE to determine the absence of a lookup value. Once the lookup value is exceeded (perhaps, after examining only a few entries) a result is returned, and then checked by the IF function. This could be preferrable to searching an entire 1000+ row table. Tuning an application is art! Right?
