#VALUE! on correct formulas

  • Thread starter Thread starter mike allen
  • Start date Start date
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
 
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.
 
=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.
 
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
 
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.
 
Back
Top