INDEX & MATCH multiple values problem

**clarkejoe** · 08-19-2011, 08:04 AM

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** · 08-19-2011, 08:12 AM

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

**clarkejoe** · 08-19-2011, 08:24 AM

Originally Posted by Whizbang

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

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

**Whizbang** · 08-19-2011, 08:52 AM

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

=IFERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"") & " - " & IFERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(2:2)),2)),"")

**Marcol** · 08-19-2011, 09:37 AM

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

Option Explicit

Function GetAppointments(ForDate As Date) As String
    Dim strApts As String
    Dim FirstRow As Long, LastRow As Long
    Dim Cell As Range
    Dim ws As Worksheet

    Set ws = ActiveWorkbook.Sheets("Sheet1")
    With ws
        FirstRow = .Range("C1").End(xlDown).Row
        LastRow = .Range("C" & Rows.Count).End(xlUp).Row

        For Each Cell In .Range(.Cells(FirstRow, "C"), .Cells(LastRow, "C"))
            If Cell = ForDate Then
                strApts = strApts & Cell.Offset(0, -1) & Chr(10)
            End If
        Next
    End With
    If strApts <> "" Then strApts = Left(strApts, Len(strApts) - 1)
    
    GetAppointments = strApts
    
End Function

With your example Sheet2
In A5

=GetAppointments(DATEVALUE(C$10&$A$1&$B$1))

Drag Across to Column G

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

Hope this hepls

**clarkejoe** · 08-19-2011, 12:47 PM

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

This is the formula I'm using:

=IFERROR(INDEX('Portfolio Data'!$B$4:$B$200,SMALL(IF(TEXT(DATE('Next Month''s Calendar'!$B$1,'Calendar Data'!$B$27,'Next Month''s Calendar'!E$32),"dd/mm/yyyy")=TEXT('Portfolio Data'!$C$4:$C$200,"dd/mm/yyyy"),ROW('Portfolio Data'!$C$4:$C$200)),ROW(1:1))),"")

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** · 08-19-2011, 01:21 PM

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

**clarkejoe** · 08-19-2011, 02:18 PM

Yes indeedy

**Whizbang** · 08-22-2011, 08:30 AM

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.

INDEX & MATCH multiple values problem

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

INDEX & MATCH multiple values problem

Re: INDEX & MATCH multiple values problem

Re: INDEX & MATCH multiple values problem

Re: INDEX & MATCH multiple values problem

Re: INDEX & MATCH multiple values problem

Re: INDEX & MATCH multiple values problem

Re: INDEX & MATCH multiple values problem

Re: INDEX & MATCH multiple values problem

Re: INDEX & MATCH multiple values problem

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions