Here's a common example: you want a formula with a VLOOKUP which either looks
up the number you want, or returns zero if it's not in the table -- this is
so that calculations based on that formula use zero and calculate something
meaningful rather than result in #N/A!. So you have to do this:

IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

Of course that "..." can be pretty long and hairy. And it's inefficient
too, because that long VLOOKUP has to be calculated twice. Isn't there any
way of writing a formula so it calculates the VLOOKUP once, stored the
result, and uses just the result in the rest of the formula? That is:

R=VLOOKUP(...);IF(ISNA(R),0,R)

I realize you can insert columns just to hold temporary results, and then
hide them, but when you copy them and paste them outside Excel, you get the
hidden columns too, and I don't want them.

Thanks!

--
Andy Smith
Senior Systems Analyst
Standard & Poor's, NYC

You can treat any empty cell as if it were a "variable". Just assign your
VLOOKUP formula to one of them and then reference that cell in your IF
function.

--
Rick (MVP - Excel)


"Andy Smith" wrote in message
...
Here's a common example: you want a formula with a VLOOKUP which either
looks
up the number you want, or returns zero if it's not in the table -- this
is
so that calculations based on that formula use zero and calculate
something
meaningful rather than result in #N/A!. So you have to do this:

IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

Of course that "..." can be pretty long and hairy. And it's inefficient
too, because that long VLOOKUP has to be calculated twice. Isn't there
any
way of writing a formula so it calculates the VLOOKUP once, stored the
result, and uses just the result in the rest of the formula? That is:

R=VLOOKUP(...);IF(ISNA(R),0,R)

I realize you can insert columns just to hold temporary results, and then
hide them, but when you copy them and paste them outside Excel, you get
the
hidden columns too, and I don't want them.

Thanks!

--
Andy Smith
Senior Systems Analyst
Standard & Poor's, NYC

Check your other posts.

Andy Smith wrote:

Here's a common example: you want a formula with a VLOOKUP which either looks
up the number you want, or returns zero if it's not in the table -- this is
so that calculations based on that formula use zero and calculate something
meaningful rather than result in #N/A!. So you have to do this:

IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

Of course that "..." can be pretty long and hairy. And it's inefficient
too, because that long VLOOKUP has to be calculated twice. Isn't there any
way of writing a formula so it calculates the VLOOKUP once, stored the
result, and uses just the result in the rest of the formula? That is:

R=VLOOKUP(...);IF(ISNA(R),0,R)

I realize you can insert columns just to hold temporary results, and then
hide them, but when you copy them and paste them outside Excel, you get the
hidden columns too, and I don't want them.

Thanks!

--
Andy Smith
Senior Systems Analyst
Standard & Poor's, NYC

--

Dave Peterson