The link in your question is good, but it needs to be adjusted as follows.
=IFERROR(INDIRECT("B"&SMALL(IF(X25=$D$17:$D$22;ROW($D$17:$D$22);"");ROW($D$17:$D$22)-16));"")
Enter as an array formula {}
by pressing Ctrl+Shift+Enter. The result is 4, 5, and 6 as you requested.
Adjustments:
INDIRECT("B"&
— In your case, you want the value from column B, not the index.X25=
instead of"a"=
because you want the value from that row.ROW($D$17:$D$22)-16
instead ofROW()-2
because the row number is from the D column, not from the location where you're putting the formula.
EDIT:
The OR
function itself will not work in an array formula, so use the +
operator instead.
=IFERROR(INDIRECT("B"&SMALL(IF((X25=$D$17:$D$22)+(X25=$E$17:$E$22);ROW($D$17:$D$22);"");ROW($D$17:$D$22)-16));"")
Result: 2, 3, 6
Regarding your other comment, it's not directly based on the size of the source — the resulting array is actually that big, only some of the results are empty strings (""
in the formula). You could add a filter to hide empty results by selecting column Y, going to Data > Auto Filter and unchecking (empty).