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

 

[Arithos]

I think I know what the issue is here, when you use index/match looking for one specific value, from C1, it will only return the first match for all cases.
For this problem I would resort to a macro to quickly look for todays date. I do not know any other way of doing this with a "one-cell" formula. I hope someone can provide it tho

 

[DLB20720]

Thanks for your response. I am the 'worlds worst' when it comes to macros and I have seen very similar examples using formulas that return multiple values for the same unique criteria so I am fairly certain this can be done.

 

[Arithos]

I'm also fairly certain of the same thing, however, I do not know the solution. I would normally have used VBA, if you do not need it solved in a single cell however (using a column to help instead) I think I would be able to come up with a solution. Let me know if that is of any interest.

 

[DLB20720]

Arithos, I'm not completely certain what you mean when you say 'not need it solved in a single cell'. As there may at times be several hundred stocks reporting earnings on a single day, having results displayed in a column would be the best way for me to view and manipulate the results. Hopefully that better explains my needs.

I am not opposed to using VBA, but have very limited knowledge of it and can only write the most basic macros. I'm learning but doing something like this is way over my head. I appreciate your help though!

 

[RCBricker]

I have a couple questions.

first is column C, you are using =today() in each cell in your range of data?
Column B I am assuming is a schedule of dates of expected arrival of Earnings announcement?
Where are you housing your answer?

If Column B is a schedule of dates and you are comparing that date to the value in column C then just write a quick If statement that returns the symbol and populates your answer cells.

This assumes you no data in column D

=IF(C2=B2,INDEX(A2:A8,MATCH(C2,B2,1)),"")

would that work?

When I run it with dummy stocks against today's date in column C and dates created for Column B the formula brings back only the two stocks I assigned 10/27/2014 as a date in column B.

 

[DLB20720]

RCBricker, I am trying to get my results listed in column D. Regarding the value "TODAY()" in C1, I just used that so I don't have to change the dates daily and ..... when referring to that value in my formulas I used an absolute reference ($C$1). You are correct about column B; all values in column B are expected dates of Earnings Announcements for the corresponding stock in column A.

I tried your formula but modified it to get the entire ranges. The formula listed stocks (which is more than my formulas did!) and some were correct but some referred to blank cells and others referred to stocks with dates other than TODAY(). But I think that's on the right track and maybe if I modify that I can get it to work.

 

[RCBricker]

I started looking at this and realized you might not need index/match.

try this

=IF(A2="","",IF(B2=$C$1,A2,""))

 

[DLB20720]

That formula pulls the stock symbol across the same row into the corresponding column D cell. My original goal was to pull all the symbols into a continuous list in column D, but I am also planning to run additional formulas against stocks listed in column D which will put them in a list and allow me to further manipulate the data. As soon as I saw your formula I had one of those "duh!" moments.

This works too - =IF(B2=$C$1, A2,"") and copy down.

 

[Arithos]

As long as copying the formula down for 8000 rows is ok, those formulas will work nicely, However, a formula to return all the stock tickers in question at the top of column D, is what you want. And this is using a helping column I'll look into it again when I can.