Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))} |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))} |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIFS formula using dates and text as criteria | Excel Discussion (Misc queries) | |||
New 2007 Sumifs formula - is there a new multiple criteria vlookup | Excel Worksheet Functions | |||
Sumifs formula in Excel 2007 | Excel Discussion (Misc queries) | |||
sumifs formula in excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 - SUMIFS formula use between tabs | Excel Discussion (Misc queries) |