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

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))}

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

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))))


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Westy" wrote:

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))}

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

see your other post

--
Biff
Microsoft Excel MVP


"Westy" wrote in message
...
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))}