Excel XP array formuls not working?

  • Thread starter Thread starter Rf
  • Start date Start date
R

Rf

I was working on a sheet the other day trying to use if
(and(<arguments here>)) on an array formula but could not
get the appropriate results. I talked to someone else
where I work who also commented that their existing array
formulas that worked before the XP version of Excel, now
no longer work. Is there something new in the syntax? I
didnt see anything in help.

Here are the specifics: 2 worksheets, 2 lookup values
(need to match both), 1 return value.

Sheet1
cust# chain# Sales$
10 15 $5

Sheet2
cust# chain# Sales$
10 15 ={(if(and(sheett2!a2=sheet1!a2:axx,
sheet1!b2=sheet1!b2:bxx),sheet1:c1),"nomatch")}

I typed this formula by hand, so ti may not be correct
here, but it was correct in my sheet because this formula
would always return the text string and not the $amt from
the Sales$ column.

Any ideas?
Thanks

Rf
 
If I understand you properly, this will work without being array entered.
=SUMPRODUCT((E5:E8=715)*(F5:F8=12)*G5:G8)
you may substitute the number for a cell reference
=SUMPRODUCT((E5:E8=a1)*(F5:F8=a2)*G5:G8)
 
Thanks for the reply, but I'm not sure this will work
since the arrays are not the same size, and according to
the help file, if they are not, a #Value error occurs.
Can the sumproduct function be used to retun values other
than the product of the arrays? This is not very clear
from the help file.

Robert
 
Make them the same size by using the longest as your last row. Be advised
that you cannot use a:a but you could use a1:a65536. DONT
 
Back
Top