If it helps, here is how the J2 formula works:
=IFERROR(INDEX(DB!$A$2:$I$5,AGGREGATE(15,6,(ROW(DB!$A$2:$A$5)-ROW(DB!$A$1))/ISNUMBER(SEARCH($J$1,DB!$A$2:$A$5)),ROWS(J$1:J1)),1),"")
I'll start on the interior of the formula:
SEARCH($J$1,DB!$A$2:$A$5)
This uses the text string that you input in cell J1, and it searches the text strings found in the range described in the main data table...in this case DB!$A$2:$A$5. When SEARCH finds the J1 text string anywhere inside any of those other cells, it will return the position number where the first character matches in that string. For example, a search for "wo" will return a value of 8 when it encounters the song title "What a Wonderful World" and a value of 1 when it encounters the song title "Woman". Any song titles that do not contain the string will return a #VALUE error. The net result of the SEARCH function then is an array whose elements are either a number (meaning that the string was found) or an error. In the example in post #5, that array looks like this: {#VALUE!;#VALUE!;8;1}
We pass the array resulting from the SEARCH function to the ISNUMBER function, and that simply evaluates whether each element in the array is a number, resulting in an array of TRUE's and FALSE's. The same array would then evaluate as: {FALSE;FALSE;TRUE;TRUE}
Because we want to know where in the main data table these matching entries can be found, we create an array whose elements begins at 1 for the first row of song data and increase by one for each row in the data table. This is conveniently done by directly referencing the data table using the ROW function:
(ROW(DB!$A$2:$A$5)-ROW(DB!$A$1))
The first ROW function in this formula evaluates as {2;3;4;5}, and since we ultimately want to use this approach for creating an actual index to the data table, we subtract off the row number of the row just above the first row...in this case ROW(DB!$A$1), which evaluates as {1}. And then the final indexing array will be {1;2;3;4}...a nice convenient index for the data table.
In the previous paragraph, note that the choice of referencing the data table is completely arbitrary. If you had worksheets named Sheet2 and Sheet3, you could generate the same array with
(ROW(Sheet3!$G$28:$H$31)-ROW(Sheet2!$P$27))
Note that I've mixed sheet names and arbitrarily used different combinations of columns and rows...it doesn't matter...the only thing that really matters is that we lock the row numbers to generate enough elements in the array and that we subtract off the correct amount so that the first element becomes a one. In this case we would have {28;29;30;31} minus {27}, which generates the very same array of {1;2;3;4}. The ROW construction is confusing enough when convenient landmarks on the actual table are referenced, so there is no good reason to do otherwise.
Picking back up with the two arrays that have been generated, we divide the indexing array (from the ROW functions) by the logical array (from the ISNUMBER function that checked whether the string was found in each song title)...so the math looks like this:
{1;2;3;4} / {FALSE;FALSE;TRUE;TRUE}
The division operator coerces Excel to treat FALSE as 0 and TRUE as 1, so we have: {1;2;3;4} / {0;0;1;1}
This is equivalent to: { 1/0 ; 2/0 ; 3/1 ; 4/1 }
And since division by 0 is undefined, the resultant array is: {#DIV/0!;#DIV/0!;3;4}
This tells us that the matching string is found in table rows 3 and 4, and we want to ignore any errors (like the #DIV/0! error).
Fortunately, this is readily achieved by operating on this array with the AGGREGATE function. I'll show this more clearly by swapping out the formulas just discussed with the array that they generate...then the AGGREGATE function would look like this:
AGGREGATE(15,6, {#DIV/0!;#DIV/0!;3;4} ,ROWS(J$1:J1) )
The 15 option tells AGGREGATE to return the nth smallest value, where n is given by the last argument, ROWS(J$1:J1). Let's look at that for a moment.. ROWS(J$1:J1) tells us how many rows are found between J$1 and J1, so the answer is just 1. But note that this range is fixed at one end (J$1) and it floats at the other end (J1), so as this formula is pulled down to populate a total of 10 cells (so that as many as 10 songs with partial string matches will be returned), this ROWS construction will return a value of 1 for ROWS(J$1:J1), then 2 for ROWS(J$1:J2), then 3 for ROWS(J$1:J3), and so on. So when n=1, AGGREGATE looks at the array and returns the smallest value, which is 3, and recall that this represents the data table row index where a match is found.
Essentially, the AGGREGATE function generates a list of data table row index numbers that work well with the INDEX function so that the appropriate information can be returned from the main data table. In this case, the first result is INDEX(DB!$A$2:$I$5, 3 ,1), which returns the contents of the third row and first column in the data table DB!$A$2:$I$5, and
the second result is INDEX(DB!$A$2:$I$5, 4 ,1), which returns the contents of the fourth row and first column in the data table DB!$A$2:$I$5.
What about those #DIV/0! errors in the array used by AGGREGATE? The "6" option in AGGREGATE tells the function to ignore them, so we conveniently avoid crashing the formula with an error.
What about cases where AGGREGATE runs out of elements to return? For example, is the case described here, we have two numeric elements (3 and 4), and when AGGREGATE goes to return the 3rd smallest value and finds only #DIV/0! errors remaining in the array, it will generate a #NUM! error. The INDEX function will object to being fed this error, resulting in a #NUM! being returned. To avoid this, and clean up the appearance of the partial-search table, we wrap the entire INDEX function with an IFERROR function, returning a blank should this type of error occur:
=IFERROR(INDEX(DB!$A$2:$I$5,AGGREGATE(15,6,(ROW(DB!$A$2:$A$5)-ROW(DB!$A$1))/ISNUMBER(SEARCH($J$1,DB!$A$2:$A$5)),ROWS(I$1:I1)),9),"")
