|
Re: Find & Retrieve a Set of Data
Shure
First I'll divide this formula on several parts.
Code:
=IFERROR(INDEX(B3:B23,SMALL(IF(F2=A3:A23,ROW(INDIRECT("1:"&ROWS(3:23))),""),ROW(INDIRECT("1:"&ROWS(3:23))))),"")
First is
Code:
=IF(F2=A3:A23,ROW(INDIRECT("1:"&ROWS(3:23))),"")
This formula checks availability of the item selected in dropdown list in A3:A23. If so it'd return their row number generated with formula
Code:
ROW(INDIRECT("1:"&ROWS(3:23)))
Otherwise it'd return blank.
This trick prepares array for SMALL() function. This Array something like this
Code:
"","',"","","","","","","",12,13,14,15,16,17,18,19
Each of this item separated with comma means cell. So without SMALL() function you will get your values somwhere at the bottom. SMALL() allows you to gater 'em at the top. Bur SMALL() has two arguments: RANGE and K. So for K is also
Code:
ROW(INDIRECT("1:"&ROWS(3:23))),"")
'coz it'd return number like 1,2,3,4,5,6... for each cell in the array.
And now it's a time for use INDEX() function after u have got the row number.
So final formula is
Code:
=IFERROR(INDEX(B3:B23,SMALL(IF(F2=A3:A23,ROW(INDIRECT("1:"&ROWS(3:23))),""),ROW(INDIRECT("1:"&ROWS(3:23))))),"")
included in IFERROR() in order to get rid of #NUM! error,,.
Your issue could be done with the help of using helper columns, but this formula gathers three columns into one.
|