Extract Data from a Column based on Two Different Criteria

[Cknight]

I have searched but not been able to find a solution anywhere. I'm assuming I have to use an array of some sort. I'm trying to simplify my data and still have it make sense.

I essentially have two columns on one sheet (Sheet1). Column A is all months.
COLUMN A COLUMN B
JAN AB
JAN AC
FEB BA
FEB BB
MAR CA
MAR CB
APR DA
MAY EA
JUN FA
JAN AD
JUL GA
JUL GB

On Another sheet (Sheet2), I need to pull everything in sheet 1,column B that has Jan or Feb. So my output on sheet 2 would be the 2(or 3 or 4; the data does change all the time) months I need in column A which I would manually enter and column B would be the output.

COLUMN A COLUMN B
JAN AB
FEB AC
BA
BB
AD

Essentially, I need a nested OR statement to index Sheet1, Column B while matching Sheet 1, Column A and all instances of Sheet 2, Column B

I have attached an excel workbook if it is easier to work with.

Thanks in advance for any help!

 

[bosco_yip]

Hi,

See attachment.

Regards

 

[Nebu]

Hi:

A Non-array solution:

=IFERROR(INDEX(Sheet1!$B$2:$B$13,AGGREGATE(15,6,(ROW(Sheet1!$B$2:$B$13)-MIN(ROW(Sheet1!$B$2:$B$13))+1)/(($A$2=Sheet1!$A$2:$A$13)+($A$3=Sheet1!$A$2:$A$13)),ROW(A1))),"")

Thanks

 

[Cknight]

Hi:

A Non-array solution:

=IFERROR(INDEX(Sheet1!$B$2:$B$13,AGGREGATE(15,6,(ROW(Sheet1!$B$2:$B$13)-MIN(ROW(Sheet1!$B$2:$B$13))+1)/(($A$2=Sheet1!$A$2:$A$13)+($A$3=Sheet1!$A$2:$A$13)),ROW(A1))),"")

Thanks

Nebu-
Is there a way to account for blanks in Sheet2, Column A? I would need the formula to account for 30 rows sometimes for example.
So this portion of the formula: (($A$2=Sheet1!$A$2:$A$13)+($A$3=Sheet1!$A$2:$A$13))
would need to be ($A$2=Sheet1!$A$2:$A$13)+($A$3=Sheet1!$A$2:$A$13))(($A$4=Sheet1!$A$2:$A$13)+($A$5=Sheet1!$A$2:$A$13)), etc

When I have multiple blanks in a2:a31, it does not return the correct data

 

[Nebu]

Hi:

I am not sure what you meant by blanks can you upload a sample file?

Thanks

 

[Cknight]

Hi:

I am not sure what you meant by blanks can you upload a sample file?

Thanks

In the Aggregate Result Columns D:F on the Output sheet, you'll see I included additional criteria in the formula. I had it checking A4, A5, A6 etc and to include that in the output of data as well. The problem that arises that if A4, A5, A6, etc is empty or blank, it returns incorrect data.

Theoretically, I was hoping to have Columns D:G return the same data even if it is referencing A2:A30 in the aggregate formula to see if that it is included in the Data sheet;Column A

Hope that makes sense.

 

[bosco_yip]

See revised formula attachment

Regards

 

[Cknight]

See revised formula attachment

Regards

That works perfectly! Thanks to all for their help!