http://home.pacbell.net/beban/

That website offers some user-defined-functions that all inter-relate. The
one you want to use here is VLOOKUPS. It's an array formula. You will need
to copy the code for VLOOKUPS and a few other supporting functions into a
module in your workbook. Copy the code for these functions into a standard module:

VLookups
ArrayCountIf
ArrayDimensions
MakeArray

Then, on sheet2, enter a standard VLOOKUP type formula like this:

=VLOOKUPS("Y",Sheet1!$B$2:$D$10000,3)

When you press Enter, a message will appear in the cell telling you how many cells downward you need to select.

Highlight the cell and drag down enough cells below to complete the requested number (or more to allow for expansion), press F2, then CTRL-SHIFT-ENTER to activate the array.



http://www.excelforum.com/attachment...kups-test1.xls