Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Using Excel 2007 Beta 2.
I have two cell ranges (B2:G2 and N1:N13), each of which contains a list of non-repeating integer values in ascending order. I want a formula that computes the number of values in the first range that appear in the second range. If I use scratch cells (say, AA2:AE2), I can create an array formula: {=MATCH(B2:G2,N1:N13,0)} that fills the scratch cells with an index for each value in B2:G2 that matches a value in N1:N13, and #N/A for each value in B2:G2 that is absent from N1:N13. The value I want can then be computed by: =COUNT(AA2:AE2) Is there a way to nest these formulas so I don't have to define scratch cells for the intermediate step? Is there some simpler means of computation that I've overlooked? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Array Formula w/ COLUMN & MATCH FUNCTIONS | Excel Discussion (Misc queries) | |||
large formula question - Max nested functions | Excel Worksheet Functions | |||
Nested IF and MID functions | Excel Worksheet Functions | |||
Array functions ARGHH! | Excel Worksheet Functions | |||
Array Functions from Alan Beban | Excel Worksheet Functions |