Array formula - can you break it down and explain how it works

Hi,

I'd be glad to break it down for you, so you can update it in the future (if you decide not to go the pivot table route, which is probably the better way to go).

First I'll tackle the Logical Test and True Portion of the initial IF() function IF(ROW()-6>SUMPRODUCT((Sheet1!$B$3:$B$10=Sheet2!$B$3)*1),"",. This tells excel that if we've retrieved all the matching records, then return a blank string "" instead

ROW()-6 I use to retrieve the current "record number", since you're starting at row 7 on Sheet2. Since ROW() would return 7, i subtract 6 so the first would return 1, then 2, etc. I'll substitute this part of the formula with the MatchNum for easier readability. This also appears later in the original formula as well.

SUMPRODUCT((Sheet1!$B$3:$B$10=Sheet2!$B$3)*1) I use to retrieve the number of matching records. This checks the range Sheet1!B3:B10, which has the dates, and checks how many match the date you enter at Sheet2!B3. I could have also used COUNTIF() for this; I decided to use sumproduct in case you want to use any other matching criteria from the first page. I'll substitute this with the NumOfMatchingRecords for easier readability.  



The other IF() function in there, IF((Sheet1!$B$3:$B$10=Sheet2!$B$3),ROW(Sheet1!C$3:C$10)), would not work by itself. However, as part of the array formula, this returns an array of each matching row number where the date field (Sheet1!B3:B10) matches the specified date (Sheet2!B3). The ROW() function, like above with MatchNum, simply returns the row number. As part of the array formula, it returns all of the matching row numbers. I use column C on Sheet1 in the initial formula, though I could have used B or any other column, as long as the rows (3:10) match the rows from the logical test. I'll substitute this with MatchingRows for easier readability.


The SMALL() function in there, SMALL(MatchingRows,MatchNum), is used to return the n-th matching row number from the list (matchingrows), based on the current record number (matchnum). I'll substitute this with MatchingRowNumber for easier readability.


We're looking now at the portion MatchingRowNumber-MIN(ROW(Sheet1!C$3:C$10))+1. One problem we face is that MatchingRowNumber returns the actual row number (3 through 10, in your example). For use in the INDEX() function later, we would actually want it to return the relative row number (1 through 8, in your example) within the dataset.  To fix this, I subtract the portion MIN(ROW(Sheet1!C$3:C$10)). This returns the minimum row number of the given array. In your spreadsheet, this is 3. I then add 1, to get us the relative row number.
For example, if we wanted the 1st match with a specified date of 8/27/09, then MatchingRowNumber returns 7. However, that is the 5th row within that dataset. If we subtract 3 (MIN(ROW())) and add 1, we get the value 5 that we want. I'll substitute WhichRelativeMatchingRow for easier readability.


We are now looking at the INDEX() portion of this, INDEX(Sheet1!C$3:C$10,WhichRelativeMatchingRow). This simply returns the value in the specified datarange (Sheet1!C3:C10) at the relative matching row number. Using our previous example, WhichRelativeMatchingRow would be the value "5". The 5th value in our specified datarange here is "John", so that is what gets returned. I left the "$" off before the column C so that it would change to D,E,F when you copied the column to the right (for gross calls, dead air, call backs).

This should explain the array formula for you.  I'm going to make another post to explain how to do all this using named ranges, which will make the formula this readable in your spreadsheet.  I'd do it here, but this is obviously long enough as it is :)

Matt