Display multiple row with data according to ID value

I want to ask as well about this.

For example:

One ID has multiple rows with value (one row is equal to one dependent).

So ID:001 Has 4 rows (4 dependents)

001 | Marie | 03/23 | Female

001 | Raffy | 11/20 | Male

001 | Luffy | 05/06 | Male

001 | Boney | 01/01 | Female

How can I display it when I type his ID and all of these data should appear regardless of how many rows/dependents an ID has?

--

Done so far: =Vlookup(E3,A2:D100,2,False)

--

I have done VLOOKUP but all I'm getting is the first row's value. I want all of the other values associated with A2's value (001) to be displayed.

Solution 1:

If you want to use the array formula, you may try Index+Small+If.

On my sample, I use =IFERROR(INDEX(B$2:B$7,SMALL(IF($A$2:$A$7=$G$2,ROW($2:$7)-1,4^8),ROW(A1))),""), then press Ctrl + Shift +Enter.

Then fill down to find the persons who match the ID. Then fill in to the right and find other information about this person.

Choose the cells of Date, set Date format.

Solution 2:

This is what the FILTER() function was made for. Just use:

=FILTER(  A2:D100,  A2:A100=E3  )

and all is displayed for the given ID, no sorting of the data required.

Basically it says take this range (A2:D100) and look for any records in the first column (A2:A100) that match the ID value in cell E3 and return EVERY row that matches.