Snasui Excel Forum

Hi Dear,

I have data as in attached
I have data as per below
A ruler ink
B pen ink book


if I type ink it would return A,B but if I type book it would return only B.

What formula can do this?


Best wishes

Try it
C8=INDEX({"A","B";"B",""},AGGREGATE(15,6,{1;2},ROWS(C$8:C8)),MATCH(B$8,{"ink","book"},0))

Thanks but how can we don't need type A,B in array string

Please try

=IFERROR(INDEX($B$4:$B$5,AGGREGATE(15,6,ROW($B$4:$B$5)/(MMULT(N($C$4:$I$5=$B$8),ROW(A$1:INDEX(A:A,COLUMNS($C$4:$I$5)))^0)>0),ROWS(C$8:C8))-ROW($B$4)+1),"")

Thank you