CODEWITHSUNDEEP
excelexcel-formula
Ankit
Ankit

Reputation: 801

Formula to Get data for a specific date

Date        Time            Country         Desc
16/04/14    8.30 am         India           abcd
16/04/14    9.30 am         India           pqrs
17/04/14    9.30 am         India           xyz
17/04/14    10.30 am        India           abcd

I wish to get all the data of 16/04/14 in another sheet using formula. Vlookup takes only the first row data as reference is 16/04/14. Is there any other way to get data.

Upvotes: 0

Views: 7451

Answers (1)

L42
L42

Reputation: 19727

Try this then:

Assuming you have this data in Sheet1:

enter image description here

And you want to get all matches of dates in Sheet2 like this:

enter image description here

What you need to do is use this formula:
=IFERROR(INDEX(Sheet1!A$1:A$5,SMALL((IF(Sheet1!$A$1:$A$5=$E$1,ROW(Sheet1!A$1:A$5))),ROW($A1)),1),"")
Above is an Array Formula entered by pressing Ctrl+Shift+Enter.
Enter it in A2 then copy on the remaining cells.
For our example, I copied it until Row 10 (A2:D10).
Then you just need to type in the value you want displayed in E1 as shown in the picture.
Then all the columns will populate with the data that you need.
Hope this helps.

Important: The ranges in the formula needs to be adjusted to suit your needs.

Upvotes: 2

Related Questions