#VALUE! on correct formulas

M

mike allen

I am using 'vlookup' and 'match' functions that result in #VALUE!, but when
I hit function wizard, it shows the correct number value I am looking for
and expecting in the result area. Apparently my syntax, etc. is accurate.
Why, then, does #VALUE! show up when everything is ok? thanks, mike allen
 
H

Harlan Grove

mike allen wrote...
I am using 'vlookup' and 'match' functions that result in #VALUE!, but when
I hit function wizard, it shows the correct number value I am looking for
and expecting in the result area. Apparently my syntax, etc. is accurate.
Why, then, does #VALUE! show up when everything is ok? thanks, mike
Click to expand...
allen

In an ideal world you'd get the same result in the worksheet cell that
you get in the formula wizard. However, this is Microsoft we're talking
about, so we're nowhere close to that ideal world. Translation: several
expressions evaluate differently in the formula wizard and worksheet
cells.

Show your *ENTIRE* formula in a follow-up.
 
M

mike allen

=MATCH(MONTH(A17),MONTH($D$2:$D$10))

where A17 is a date (6/15/05) and $D$2:$D:$10 is an array of dates (4/29/05,
5/31/05, 6/30/05, 7/29/05...), all clean dates I might add. The answer here
is simply 3 via function wizard. Very odd.
 
M

mike allen

well, as odd as that sounds to me, it works. it simpy puts {} around the
formula. why in the world would it require that to work? thanks, mike
allen
 
K

Ken Wright

And note also that you *cannot* just put these brackets in manually - it is
*not* the same thing :)

If you take the time to read up on them, then you may find that arrays can
be a very powerful new tool in your Excel arsenal.
 
Top