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