INDEX MATCH / return multiple values matching a unique criteria

DLB20720

Board Regular
Joined
Sep 29, 2013
Messages
117
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

Board Regular
Joined
Sep 29, 2013
Messages
117
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

Banned user
Joined
May 24, 2014
Messages
1,336
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

Banned user
Joined
May 24, 2014
Messages
1,336
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,686
Messages
5,637,827
Members
416,984
Latest member
dee10

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top