Please enable JavaScript to view the comments powered by Disqus.


INDEX(...Sheet1!$B$2:$B$100,SUMPRODUCT(...--(...Sheet1!$B$2:$B$100<>"")*1))

Using SUMPRODUCT() to validate a criteria and INDEX() to retrive a value.

The SUMPRODUCT function checks for the non blank cells and sum ups the total count. The count is feed to INDEX that bring back the respective data from the array.


Used Functions

Category: Lookup and reference

Uses an index to choose a value from a reference or array

Syntax

INDEX(array, row_num, [column_num])

Category: Math and trigonometry

Returns the sum of the products of corresponding array components

Syntax

SUMPRODUCT(array1, [array2], [array3], ...)

Lets chat on this