This will work in Excel 2010 or later, though I'm afraid I could not say if it will also work for the Mac.
There are more generic solutions available, which will work for data in any number of tables, not just two. However, they are necessarily rather complex. What's more, since you in fact have only two tables, it is probably just as well to present an alternative (though less flexible) solution to that end.
=IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))+SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0)),"",IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0)),INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))))
Copy down and to the right as required.
This can be abbreviated, though again, the techniques involved are a touch complex. I do not mean to patronise; I simply thought you might find a slightly longer, though perhaps more readily comprehensible solution of more use.
As to an explanation:
The part:
SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
counts how many entries in the ITEM DESCRIPTION column of the ProductList table are non-blank. COUNTIF is the usual choice to this end, though the two possible set-ups using that function would require that either the blanks in your range are "genuine" blanks (and so not the null string "" e.g. as a result of formulas in those cells), or that the entries are of a consistent datatype. Since I cannot be sure of either of these facts from inspecting your sample, a test for blankness using SUMPRODUCT - which is certain to work whatever the answers to the above - is the more rigorous choice.
Similarly:
SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0))
counts how many entries in the ITEM DESCRIPTION column of the ProductList2 table are non-blank.
As such, the initial clause:
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))+SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0)),""
means that, in rows beyond the total number of non-blank entries in the ITEM DESCRIPTION column of both tables, a blank will be returned.
Even better is to enter these two constructions into a couple of cells within the worksheet somewhere and reference those in the formulas instead. As such, they will need to be evaluated just once, as opposed to being calculated by each iteration of the formula. For example, if you put in G1:
=SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
and in H1:
=SUMPRODUCT(0+(LEN(ProductList2[ITEM DESCRIPTION])>0))
then the main formula becomes:
=IF(ROWS($1:1)>$G$1+$H$1,"",IF(ROWS($1:1)>$G$1,INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-$G$1),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))))
and will be much more efficient.
Note that this set-up which involves referencing a count is preferable to a potentially resource-heavy IFERROR one, an explanation as to why you can find here:
Look up a value in a list and return ALL multiple corresponding values
Anyway, if this initial clause is FALSE, we then move on to the second conditional statement, i.e.:
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
which, in a similar way to the above, tells us that, in rows beyond the total number of non-blank entries in the ITEM DESCRIPTION column of the ProductList table, we will know to concentrate on the other table, i.e. ProductList2. Otherwise, if the above is FALSE, we will direct our attention towards the first table, ProductList.
I will look at two examples, since, although the constructions for each are virtually identical, there is a small but necessary difference in one.
Using the data you posted and taking the formula in the first row as an example, i.e.:
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0)),INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A)))
we see that the initial clause, i.e.:
IF(ROWS($1:1)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
which is:
IF(1>3
is clearly FALSE, and we thus move on to the construction comprising the value_if_false clause in this IF statement, i.e.:
INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))
AGGREGATE (2010 or later only) offers us a nice, non-CSE alternative construction to the standard CSE set-ups with LARGE or SMALL.
The part:
ROW(ProductList)-MIN(ROW(ProductList))+1
is a standard construction used to generate an array of integers from 1 up to the number of rows in the table ProductList. It works since, assuming for example that this table occupied rows 4 to 7 (i.e. 4 rows in total), we would have:
{4;5;6;7}-MIN({4;5;6;7})+1
i.e.:
{4;5;6;7}-4+1
i.e.:
{4;5;6;7}-3
i.e.:
{1;2;3;4}
as required.
The part:
LEN(ProductList[ITEM DESCRIPTION])>0
checks whether each of the entries in that column have a non-zero length (i.e. are non-blank), and resolves to:
LEN({"Item1";"Item2";"";"Item3"})>0
i.e.:
{5;5;0;5}>0
i.e.:
{TRUE;TRUE;FALSE;TRUE}
We then calculate the fraction:
(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0)
which is:
({1;2;3;4})/({TRUE;TRUE;FALSE;TRUE})
to obtain:
{1;2;#DIV/0!;4}
by virtue of the fact that, when coerced by any suitable mathematical operation (here division), Boolean TRUE/FALSE values are coerced into their numerical equivalents (TRUE=1, FALSE=0).
Effectively, then, any entries which did not meet our condition of having a length greater than zero (i.e. the blank cells) have now been rendered errors.
And since, by setting AGGREGATE's second parameter to a 6, we instruct that function to ignore any error values within the range, we have thus obtained a means of eliminating any blank cells from consideration.
As such:
AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1))
which is:
AGGREGATE(15,6,{1;2;#DIV/0!;4},1)
returns 1 (the smallest value within that array, 15 being the parameter equivalent to SMALL for AGGREGATE).
We now pass that value as the row_num parameter to INDEX, such that:
INDEX(ProductList,AGGREGATE(15,6,{1;2;#DIV/0!;4},1),COLUMNS($A:A)
which is:
INDEX(ProductList,1,1)
returns "Item1", as desired.
Note the use of:
COLUMNS($A:A)
for the col_num of INDEX, which, as this formula is copied to the right, will become, successively:
COLUMNS($A:B)
i.e. 2,
COLUMNS($A:C)
i.e. 3,
etc., etc., thus giving us a means to return values from subsequent columns in the table without having to manually insert the column being referenced for each iteration.
To take another example, the formula after having copied the formula down five rows, i.e.:
IF(ROWS($1:5)>SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0)),INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,(ROW(ProductList)-MIN(ROW(ProductList))+1)/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:5)),COLUMNS($A:A)))
will, since this time the initial clause resolves to:
IF(5>3
which is TRUE, mean that we consider the other construction, i.e.:
INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A))
This is practically identical, though we here need to be a little careful with the k parameter being passed to AGGREGATE. If we were to use, as before, simply:
AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5))
we would have:
AGGREGATE(15,6,({1;2;3;4;5})/({TRUE;TRUE;FALSE;TRUE;FALSE}),ROWS($1:5))
which is:
AGGREGATE(15,6,{1;2;#DIV/0!;4;#DIV/0!},ROWS($1:5))
though, since:
ROWS($1:5)
is 5, and since the array:
{1;2;#DIV/0!;4;#DIV/0!}
does not contain a fifth smallest value, the above will return an error.
By first subtracting the number of non-blank entries in the other table from this value of 5, we ensure that we obtain the correct parameter. We therefore use:
ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))
which is:
5-3
i.e. 2.
And so we now have:
AGGREGATE(15,6,{1;2;#DIV/0!;4;#DIV/0!},2)
which is 2.
And, finally, our construction:
INDEX(ProductList2,AGGREGATE(15,6,(ROW(ProductList2)-MIN(ROW(ProductList2))+1)/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:5)-SUMPRODUCT(0+(LEN(ProductList[ITEM DESCRIPTION])>0))),COLUMNS($A:A))
which is:
INDEX(ProductList2,2,1)
returns "Item5", as required.
One final point re optimising calculation speed. Just as it is more efficient to enter the two counting formulas into cells within the actual worksheet, so too is it more efficient to make it such that the portions:
ROW(ProductList)-MIN(ROW(ProductList))+1
and:
ROW(ProductList2)-MIN(ROW(ProductList2))+1
are calculated just once each.
Although these, unlike the two SUMPRODUCT constructions, cannot, due to technical reasons, be entered into actual worksheet cells, they can be stored within Name Manager (Formulas tab).
Hence, if you defined them as e.g. Arry1 and Arry2, with Arry1 being:
=ROW(ProductList)-MIN(ROW(ProductList))+1
and Arry2 being:
=ROW(ProductList2)-MIN(ROW(ProductList2))+1
the main formula would become the more readable, more efficient:
=IF(ROWS($1:1)>$G$1+$H$1,"",IF(ROWS($1:1)>$G$1,INDEX(ProductList2,AGGREGATE(15,6,Arry2/(LEN(ProductList2[ITEM DESCRIPTION])>0),ROWS($1:1)-$G$1),COLUMNS($A:A)),INDEX(ProductList,AGGREGATE(15,6,Arry1/(LEN(ProductList[ITEM DESCRIPTION])>0),ROWS($1:1)),COLUMNS($A:A))))
Regards
