Single reference with multiple return values

Experts, I have huge excel data with format same as below (without *).

fruits **type** location *status
apple*** a abc1 good
*******b abc2 good
*******c abc3 bad
*******d abc4 bad
banana a abc6 good
*******b abc7 good
*******c abc8 bad
*******d abc9 bad

Now, Is there any function that when I find the value of fruits it will give me the type, location, and status?..Please see attached excel file for easy reference..

Thank you very much..

Please try at L4

=IFERROR(INDEX(B$3:B$33,AGGREGATE(15,6,ROW(B$3:B$33)/(LOOKUP(ROW($A$3:$A$33),ROW($A$3:$A$33)/(($A$3:$A$33>"")+($B$3:$B$33="")),$A$3:$A$33)=$K$4),ROWS(L$4:L4))-ROW(B$2)),"")

Try

=IFERROR(INDEX(B$3:B$42&"",AGGREGATE(15,6,ROW(B$3:B$42)/(LOOKUP(ROW($A$3:$A$42),ROW($A$3:$A$42)/($A$3:$A$42>""),$A$3:$A$42)=$K$4)/($B$3:$B$42<>""),ROWS(L$4:L4))-ROW(B$2)),"")

many thanks.. it works.. moreover, when I try to insert another value to be returned (date) it is not recognizing as date but a number. Tried to use format cells on the returned value but not working.. I hope there is a way I can format it as date?..Thanks again

You could try this, and then the date column can be formatted using short date:

=IFERROR(IFERROR(--INDEX(B$3:B$42&"",AGGREGATE(15,6,ROW(B$3:B$42)/(LOOKUP(ROW($A$3:$A$42),ROW($A$3:$A$42)/($A$3:$A$42>""),$A$3:$A$42)=$K$4)/($B$3:$B$42<>""),ROWS(L$4:L4))-ROW(B$2)),INDEX(B$3:B$42&"",AGGREGATE(15,6,ROW(B$3:B$42)/(LOOKUP(ROW($A$3:$A$42),ROW($A$3:$A$42)/($A$3:$A$42>""),$A$3:$A$42)=$K$4)/($B$3:$B$42<>""),ROWS(L$4:L4))-ROW(B$2))),"")

Excel 2016 (Windows) 32 bit

	K	L	M	N
1	expected result
2
3	find value	value return
4	mango	a	abc17	28/11/2020
5		b	abc18	good
6		c	abc19	bad
7		d	abc20	bad
8		c	abc21	bad
9		d	abc22	bad
10		c	abc23	bad

Sheet: Sheet4

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.