INDEX MATCH / return multiple values matching a unique criteria

[DLB20720]

I've found 30 to 40 similar examples on formulas that should solve my problem, but so far none have worked. One thing I have noticed with many of these examples is the requirement to press 'Control + Shift + Enter' for an array formula. I have Excel 2013 and no brackets appear and I'm not sure if this is necessary in 2013 as I have several other array formulas that work without doing this. I've researched this but can't find anything to indicate or rule out this requirement in 2013 but wanted to note that I tried it anyway but nothing happens. On some of the examples I found I literally cut and pasted all tables and formulas but still could not get anything to work.

My setup - I have a worksheet with roughly 8,000 stock symbols (example: AAPL, MSFT, etc) in column A. I have dates in column B that list the expected Earnings Announcement dates for the corresponding stock symbol in column A. In cell C1 I use "TODAY()" for the reference date. I'm trying to write a (copy down) formula in column D that would return all stock symbols from column A that have Earnings Announcements matching the date in C1.

I've tried 30 to 40 different formulas and nothing works. I've tried several combinations using INDEX/MATCH/COUNTIF/SMALL etc but can't figure out where I am going wrong. Hopefully someone here can offer some suggestions. Thanks!!

 

[DLB20720]

I'm still not at the point where I can look at a formula and know immediately if it is an array formula or not. Prior to encountering the formula we have been discussing, I had a lot of formulas I thought were array formulas. I know now they are just formulas on arrays. I can see differences but I know I still have a lot to learn about them.

 

[Aladin Akyurek]

I'm still not at the point where I can look at a formula and know immediately if it is an array formula or not. Prior to encountering the formula we have been discussing, I had a lot of formulas I thought were array formulas. I know now they are just formulas on arrays. I can see differences but I know I still have a lot to learn about them.

Some examples (not exhaustive) for CSE:

1. When the formula has a term testing a reference like in:

IF(X=c,Y) where X is a multicell reference and Y the test result;

Example:

IF(A2:A10="Jane",B2:B10)

2. When the formula has a term that does a pairwise evaluation like in:

(X = Y) where X and Y are multicell references;

Example:

E2:E5=X2:X5

3. When the formula has a term with multicell references acting as operands of a math operaror like in:

X / Y where C and Y are multicell references;

Example:

E2:F2 / E1:F1

4. When the formula has a term with a math operator acting on a reference like in:

X ^ N where X is multicell reference and N a number;

Example:

A1:A3^3

5. When the formula has a term with the TRANSPOSE function on a reference like in:

TRANSPOSE(X) where X is a multicell reference;

Example:

TRANSPOSE(A2:A4)

6. When the look up functions must seek for multiple look up values at once like in:

Look for X in Y where X and Y are multicell references;

Example:

MATCH(A2:A4,E2:E10,0)

a. The range-processing functions constitute exceptions. These are: CountIf(s), SumIf(s), AverageIf(s).

b. The look up functions fed with a scalar/single value to seek for constitute exceptions.

 

[DocAElstein]

Some examples (not exhaustive) for CSE:

1. When the formula has a term testing a reference like in:

IF(X=c,Y) where X is a multicell reference and Y the test result;

Example:

IF(A2:A10="Jane",B2:B10)............

. .....

.........

Re “CSE Curly Bracket Stuff”
. A wild idea for me to sleep on that is probably too naïve and is wrong: “Array” Formulas have no meaning to a computer. But at some point in the computer program it does not know the difference between VBA or Excel as I think Excel is sort of written in VBA?
. So something that can be done in one line in VBA like
Range("C2:C10").Value = Range("A2:A10").Value
must be possible in Excel. There is some mechanism in the Excel built in to protect or allow this “over a range” mechanism, that is to say prevent you changing parts of an array. So by selecting the whole range then typing in that range formula = A2:A10 with CSE, it is protected as a range and you will find that you cannot then change part of it.
. Maybe there is some parallel with the idea that this does not work
Range("C2:C10").Value = Evaluate("A2:A10")
. But this “coercing” over the range does
Range("C2:C10").Value = Evaluate("If(Row(A2:A10),A2:A10)")

. But no one really understands that one. If for example cells A2 to A10 have 2 – 10 written in them then
Range("C2:C10").Value = Evaluate("left(A2:A10,4)")
Returns the value 2 in cells A2 to A10 which is explained away by the idea that it has not been coerced “over the range” so just gives the first value….

… But that explanation cannot explain why
Range("C2:C10").Value = Evaluate("A2:A10")
Returns no value at all in cells A2 to A10

Alan

 

[DocAElstein]

Re “CSE Curly Bracket Stuff”.......
. But no one really understands that one. If for example cells A2 to A10 have 2 – 10 written in them then
Range("C2:C10").Value = Evaluate("left(A2:A10,4)")
Returns the value 2 in cells A2 to A10 which is explained away by the idea that it has not been coerced “over the range” so just gives the first value….

… But that explanation cannot explain why
Range("C2:C10").Value = Evaluate("A2:A10")
Returns no value at all in cells A2 to A10

Alan

... oops I meant C2 to C10 there...