Extracting data with two conditions

Hi all, I have been trying to extract each row that contains a specific date and status. I have tried multiple websites and for some reason I cannot solve my issue.

As seen in the picture, I wish to extract all the specified date that ALSO contains the accepted status "YES".

This formula is not related to the image I created as an example but it might give you a sense of what I have been attempting: =IF(ROWS($I$7:I7)>$K$4,"",INDEX(A$4:A$12,SMALL(IF($A$4:$A$12>=$I$4,IF($A$4:$A$12<=$J$4,ROW($A$4:$A$12)-ROW($A$4)+1)),ROWS($I$7:I7))))

extraction.png

Thank you,

Any advice would be awesome. If you need me to be more specific let me know.

- Jason

1 autofilter
2 formulas =IFERROR(INDEX(bd!A$2:A$2000,INDEX($D$1:$N$1,ROW($A1))),"")

wt about =Vlookup ?

Hi Tim,

This seems to be what I am looking for. Are you able to just explain what is happening in D1 please?

Thank you

Please see the attached file for the solution.
Some of the Formulas:

1) Sheet1, New column I.
I2:=A2&F2

2) Sheet2,
I2:=COUNTA(Sheet1!A1:A10)-1
I7:=IFERROR(MATCH($A$2&$B$2,Sheet1!$I$1:$I$10,0),"")
I8:=IFERROR(I7+MATCH($A$2&$B$2,OFFSET(INDIRECT(ADDRESS($I7+1,9,,,"Sheet1")),0,0,$I$2-$I7+1,1),0),"")
A7:=IFERROR(INDEX(Sheet1!$A$2:$I$10,MATCH($A$2&$B$2,Sheet1!$I$2:$I$10,0),MATCH(A$6,Sheet1!$A$1:$I$1,0)),"")
A8:==IFERROR(INDEX(OFFSET(INDIRECT(ADDRESS($I7+1,1,,,"Sheet1")),0,0,$I$2-$I7+1,9),MATCH($A$2&$B$2,OFFSET(INDIRECT(ADDRESS($I7+1,9,,,"Sheet1")),0,0,$I$2-$I7+1,1),0),MATCH(A$6,Sheet1!$A$1:$I$1,0)),"")

in d1 and to the right SMALL returns row numbers which meets the conditions

Thank you Imatzav, this is great.

I am going to have a play with this now. I was also looking to do one additional thing. Two or more of the columns would contain names. I would like to search multiple columns and return the number of unique names found combined in all columns. Is this possible?

Columns with names.png

Is that in Russian?

now in English
=SUM(1/COUNTIF(C2:D10,C2:D10))-1

Hi Imatzav / Tim

Thank you again for your help.

I am attempting to apply your shared knowledge whilst learning simultaneously.

I have made an error somewhere - my data is quite huge. (1000+ rows by 123 Columns) I also only want to extract specific columns and not necessarily in order. I hope this is not going to cause too many problems.


I understand we are counting all the rows -the row headers, in my case in K2 I have -3 as my data begins on the 4th row. (I hope I am correct).

K7 - This seems to have identified the first row that matches both criteria (as I know there are more than 1 match).

I7 - We want to extract the first row and then the following rows that match both criteria, if there is an error return nothing .... then I am a bit lost ....

I have removed my data to show how my raw inputted data is formatted (this is what I need to extract to sheet2)

Thank you for your support - it is awesome

Date and status extract.xls

Originally Posted by Jacehigh

Thank you Imatzav, this is great.

I am going to have a play with this now. I was also looking to do one additional thing. Two or more of the columns would contain names. I would like to search multiple columns and return the number of unique names found combined in all columns. Is this possible?

Attachment 530772

See the attached file.

Originally Posted by Jacehigh

Hi Imatzav / Tim

Thank you again for your help.

I am attempting to apply your shared knowledge whilst learning simultaneously.

I have made an error somewhere - my data is quite huge. (1000+ rows by 123 Columns) I also only want to extract specific columns and not necessarily in order. I hope this is not going to cause too many problems.


I understand we are counting all the rows -the row headers, in my case in K2 I have -3 as my data begins on the 4th row. (I hope I am correct).

K7 - This seems to have identified the first row that matches both criteria (as I know there are more than 1 match).

I7 - We want to extract the first row and then the following rows that match both criteria, if there is an error return nothing .... then I am a bit lost ....

I have removed my data to show how my raw inputted data is formatted (this is what I need to extract to sheet2)

Thank you for your support - it is awesome

Attachment 530780

See the new file. Check if this works correctly. I don't have the time right to check it more myself.

You have been more than enough help already. Works perfect. I will keep going over it until I understand it.

Thank you all for your contribution