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

 

[RCBricker]

you know you could just use filter. Filter the stocks based on the dates of earnings announcement. just select from the drop down today()

Not sure what else you are going to run against your list, but if you need a static unique list copy Col A to Column E (or wherever) and remove dups.

Rich

 

[Aladin Akyurek]

Try to post a small representative sample along with the conditions must hold and the results that you want to see based on that sample.

 

[DLB20720]

RCBricker, I am trying to avoid the use of filters because I would like to make this as automatic as possible. I had previously considered the use of a filter or Pivot Table but I'm certain there is a way to do this so Excel automatically lists (in a continuous column) any stocks reporting earnings TODAY(). After I used your formula to pull all stocks reporting earnings TODAY() into an adjacent column, I tried writing a formula that would pull them into a continuous list, but failed and haven't had much time to try again. Most of my work on my spreadsheets has to be done on the weekends when the markets are closed.

Aladin, I'll show a table below that represents the data I have and another to show what I am trying to do.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Symbol[/TD]
[TD]EA Report Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AAN[/TD]
[TD]10/27/14[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABBV[/TD]
[TD]10/31/14[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AGX[/TD]
[TD]10/21/14[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AHH[/TD]
[TD]10/31/14
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AIN[/TD]
[TD]11/03/14[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AON[/TD]
[TD]10/31/14
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AVIV[/TD]
[TD]10/31/14[/TD]
[/TR]
</tbody>[/TABLE]

The table below represents what I am trying to accomplish; pull any stocks reporting earnings TODAY() into a continuous column. Today is actually 10/31/14. I don't necessarily have to have the date column, I just did that below to illustrate what I am trying to match. The 'TODAY()' formula can be listed anywhere on my sheet for use in another formula.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ABBV[/TD]
[TD]10/31/14[/TD]
[/TR]
[TR]
[TD]AHH[/TD]
[TD]10/31/14[/TD]
[/TR]
[TR]
[TD]AON[/TD]
[TD]10/31/14[/TD]
[/TR]
[TR]
[TD]AVIV[/TD]
[TD]10/31/14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[TABLE="width: 362"]
<COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2901" width=82><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6257" width=176><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3441" width=97><TBODY>[TR]
[TD="class: xl63, width: 82, bgcolor: white"]Symbol[/TD]
[TD="class: xl63, width: 176, bgcolor: white"]EA Report Date[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 97, bgcolor: transparent, align: right"]10/31/2014[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 82, bgcolor: white"]AAN[/TD]
[TD="class: xl66, width: 176, bgcolor: white"]10/27/2014[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]Symbol[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 82, bgcolor: white"]ABBV[/TD]
[TD="class: xl67, width: 176, bgcolor: white"]10/31/2014[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]ABBV[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 82, bgcolor: white"]AGX[/TD]
[TD="class: xl66, width: 176, bgcolor: white"]10/21/2014[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]AHH[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 82, bgcolor: white"]AHH[/TD]
[TD="class: xl67, width: 176, bgcolor: white"]10/31/2014[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]AON[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 82, bgcolor: white"]AIN[/TD]
[TD="class: xl66, width: 176, bgcolor: white"]11/3/2014[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"]AVIV[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 82, bgcolor: white"]AON[/TD]
[TD="class: xl67, width: 176, bgcolor: white"]10/31/2014[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 82, bgcolor: white"]AVIV[/TD]
[TD="class: xl67, width: 176, bgcolor: white"]10/31/2014[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

E3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=E$1,
  ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(E$3:E3))),"")

 

[DLB20720]

Aladin, I tried your formula on my actual table but it only pulled up the first symbol (with incorrect date match), then nothing else. I then cut and pasted both the table and your formula in a spreadsheet and got the same result.

I've tried a few things over the last hour or so but still can't get anything to work. I won't have the opportunity to try anything else until tomorrow but will try again.

 

[Aladin Akyurek]

Aladin, I tried your formula on my actual table but it only pulled up the first symbol (with incorrect date match), then nothing else. I then cut and pasted both the table and your formula in a spreadsheet and got the same result.

I've tried a few things over the last hour or so but still can't get anything to work. I won't have the opportunity to try anything else until tomorrow but will try again.

See the workbook that implements the set up: https://dl.dropboxusercontent.com/u/65698317/DLB20720%20sublist.xlsx

 

[DLB20720]

Aladin, as soon as I opened your workbook I could see a major part of what my problem has been; I have never been able to produce the array brackets during any of my previous attempts. I could see the brackets in your workbook and your formula works as intended.

I've probably read 25 to 30 articles about this procedure for an array formula and all indicated this could be done by clicking in a cell, entering Control+Shift+Enter (C+S+E), and that the brackets would appear. They never have. After seeing the brackets in your formula, I started digging again and found 1 Microsoft article that indicated this C+S+E procedure must be entered in the Formula Bar, not the cell. So I tried that and discovered that the array brackets will only appear after an array formula has been entered in the cell and I can do this either by left clicking anywhere in the formula bar or by double left clicking in the cell, and then pressing C+S+E.

As many articles as I've read trying to resolve my issue, I'm not sure why this C+S+E procedure isn't spelled out better.

 

[Aladin Akyurek]

Aladin, as soon as I opened your workbook I could see a major part of what my problem has been; I have never been able to produce the array brackets during any of my previous attempts. I could see the brackets in your workbook and your formula works as intended.

I've probably read 25 to 30 articles about this procedure for an array formula and all indicated this could be done by clicking in a cell, entering Control+Shift+Enter (C+S+E), and that the brackets would appear. They never have. After seeing the brackets in your formula, I started digging again and found 1 Microsoft article that indicated this C+S+E procedure must be entered in the Formula Bar, not the cell. So I tried that and discovered that the array brackets will only appear after an array formula has been entered in the cell and I can do this either by left clicking anywhere in the formula bar or by double left clicking in the cell, and then pressing C+S+E.

As many articles as I've read trying to resolve my issue, I'm not sure why this C+S+E procedure isn't spelled out better.

Great. In fact, if you are typing such a formula in a cell, you can apply CSE immediately while in the cell in order to complete the formula. Goinig to the formula bar after entering a formula or pasting it, then applying CSE is also ok.

 

[DocAElstein]

Aladin, as soon as I opened your workbook I could see a major part of what my problem has been; I have never been able to produce the array brackets during any of my previous attempts. I could see the brackets in your workbook and your formula works as intended.

I've probably read 25 to 30 articles about this procedure for an array formula and all indicated this could be done by clicking in a cell, entering Control+Shift+Enter (C+S+E), and that the brackets would appear. They never have. After seeing the brackets in your formula, I started digging again and found 1 Microsoft article that indicated this C+S+E procedure must be entered in the Formula Bar, not the cell. So I tried that and discovered that the array brackets will only appear after an array formula has been entered in the cell and I can do this either by left clicking anywhere in the formula bar or by double left clicking in the cell, and then pressing C+S+E.

As many articles as I've read trying to resolve my issue, I'm not sure why this C+S+E procedure isn't spelled out better.

Hi ... maybe another variianten to try.........

That whole Array curly {} brackets stuff is a mystery to me. But I am trying to understand it and did have some success using those types of formulas in threads recently..…. To get them “in” I usually had to Select the range where I wanted it, press f2, then paste the formula in, then Ctrl+Shift+Enter. Still did not always work and I had to rely on someone giving me the file with the “Array” formula in it sometimes. I say “Array” because moderators on the board have told me VBA doe not really have Array formulas?!?!.
… I hope to try and get clued up on that stuff soon…..

 

[RCBricker]

I have found it easiest to simply complete my Array formula by pressing C+S+E. As for copying into other formulas, I just complete the first as an array and then copy paste or drag the formula. The problem I had in the beginning is knowing which formulas would be array. I use the internet when trying new formulas concepts and if they say Array I C+S+E and take note till I remember it each time.