Select the range of cells in which you want the results, say L18:L25.
Assuming you have numeric values in D4
11 and the corresponding
values to return in E4:E11, enter the following array formula and
press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the
formula into an array of cells, rather than a single cell.
=IF(ROW()-ROW(L$18)<COUNTIF(D4
11,"b"),LARGE(IF(D4
11="b",E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4
11,"b")))),0)
Change the reference to L$18 to the first cell in the results range
that contains the formula. Change the "b" to the value you want to
look up in D4
11.
This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com