New 2007 Sumifs formula - Is there a new 2 criteria vlookup?

  • Thread starter Thread starter Westy
  • Start date Start date
W

Westy

Is there a new multiple criteria vlookup in 2007 just like there is the new
sumifs formula eg does not need to be an array formula, can reference columns
instead of cell ranges etc.

I am currently using
{=INDEX('Sheet!$O$2:$O$35000,MATCH(1,('Sheet'!$Q$2:$Q$35000=D2)*('Sheet'!$M$2:$M$35000=F2),0))}
 
Hi,

Didn't you ask this question yeasterday? There are 5 new functions of use
to the general public and 8 of little interest to anyone yet. They are
sumifs, countifs, averageif, averageifs, and iferror.

In 2007 all functions can reference an entire column. You can sometimes
avoid an array by using SUMPRODUCT, for example, this returns the first
occurance of a match based on two conditions

=INDEX(C:C,SUMPRODUCT(MIN((A:A=D1)*(B:B=E1)*ROW(C:C))))
 
Back
Top