INDEX & MATCH multiple values problem

[clarkejoe]

I'm trying to create an automatically updating calendar.

I get my information of what is happening on what day from a third party API.

The data is not chronological.

Multiple things can happen on one day.

I've successfully created a INDEX and MATCH function to display the correct data in the calendar - however, if more than one event happens on a day, only the first event is shown.

Is there a way of getting the formula to return all of the matches?

Attached is a simplified (without the api) version of what I'm trying to do. If you look at Sheet1 you will see numerous appointments occur on one day but in sheet 2 only the first of those is displayed.


Thanks.

[Whizbang]

See this article:
http://office.microsoft.com/en-us/ex...001226038.aspx

[clarkejoe]

See this article:
http://office.microsoft.com/en-us/ex...001226038.aspx

Ideally it would be in the same cell if possible though.

[Whizbang]

Ok, so you would just have to concatenate two versions of the formula.

Please Login or Register  to view this content.

[Marcol]

I would be tempted to go for a UDF to handle this, concatination gets messy after a few lines, say there were 5 or 6 appointments in any day.

Try this

Please Login or Register  to view this content.

With your example Sheet2
In A5

Please Login or Register  to view this content.

Drag Across to Column G

Similarly for A7,9,11,13, and 15

Hope this hepls

[clarkejoe]

I think I'll go for the SMALL option and just create numerous rows.

This is the formula I'm using:

Please Login or Register  to view this content.

The cells are all correct and so are the names of the data sheets, but it isn't working. The formula isn't liking the SMALL nested function. Comes up with #N/A.

I can't attach the spreadsheet for confidentiality but the API imports dates as text so I've made sure what I'm looking for is in the same format as where I'm looking (superfluous formulae possibly, but erring on the side of caution) - can you see where I'm making an error?

Thanks again - you guys have been really helpful.

[Whizbang]

Are you confirming with CTRL+SHIFT+ENTER instead of just ENTER?

[clarkejoe]

Yes indeedy

[Whizbang]

Check your 'Portfolio Data'!$C$4:$C$200 range. Is there something that is evaluating to an error there? The formula works fine for me when I plug in test data in the ranges of the formula. When I throw in an #N/A in the C column, it causes the formula to fail.