VLOOKUP MATCH INDEX two conditions / criterias text and date

[John]

In Sheet1 I have a table with 5000 rows and 30 columns. I have simplifiede it
here:

A B C D E
F
Prod Categ. Descr. Code 1 Date Status
R GS Red X 12.05.2008 Stopped
B GS Yellow X 13.05.2008 Running
E RG Green X Running
X RG Blue X 10.05.2008 Stopped
H JG Blue X 08.01.2008 Stopped
F KG Red X 04.04.2008 Running
....5000 rows down...

In Sheet2 I need a table with extracted data from Sheet1 based on two
criterias: The Status from col.F is "Running", and the date in col.E has been
filed in. I don't need all 30 columns, so I have selected column A, B, C. The
result would then be like this:

Product Product category Description
B GS Yellow
F KG Red
....filled 50 rows down...

I don't think I can use a VLOOKUP as the data are not sorted accending, and
there are two criterias. I believe I need an INDEX / MATCH formula. I have
read http://www.contextures.com/xlFunctions02.html but I could't convert it
to my use. Can anyone help? One array formula in one cell filling 50 rows
down in Sheet2 would be perfect. Formulas copied out in Sheet2 A2:C50 would
also be OK.

 

[Ashish Mathur]

Dear John,

How are you? Your question is not very clear. May I request you to mail me
the workbook at (e-mail address removed). Please explain the problem very
clearly.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Bob Phillips]

=IF(ISERROR(SMALL(IF((Sheet1!$E$1:$E$50<>"")*(Sheet1!$F$1:$F$50="Running"),ROW(Sheet1!$A$1:$A$50),""),ROW($A1))),"",
INDEX(Sheet1!A$1:A$50,SMALL(IF((Sheet1!$E$1:$E$50<>"")*(Sheet1!$F$1:$F$50="Running"),ROW(Sheet1!$A$1:$A$50),""),ROW($A1))))

It is an array formula, so commit with Ctrl-Shift-Enter,

Copy acroos 3 columns and down as far as you need.

 

[Teethless mama]

The best way is to use a PIVOT table

 

[John]

I tried the Pivot Table, but I need the data shown as a table - with no
blanks. A Pivot Table groups all values in the first column, then the next,
etc. This leaves empty spaces on the left side, gradually reducing as you
move towards right. I have no data to put in the data field in the Pivot
Table. All my fields has to be put in the "row area" of the Pivot Table. I
have one version of this, but it doesen't look very nice, and I cant sort it
decending on one of the "row-fields". I have hidden all "totals".

 

[John]

Thank you Bob. This works great. One problem. I need to sort Sheet2, col.C,
but it will not sort. Is this due to the: ROW($A...)? Is there any way
around? The data in Sheet2 are constantly changing and the data in col.C
should be sorted frequently.
--
Thanks
John

=IF(ISERROR(SMALL(IF((Sheet1!$E$1:$E$50<>"")*(Sheet1!$F$1:$F$50="Running"),ROW(Sheet1!$A$1:$A$50),""),ROW($A1))),"",
INDEX(Sheet1!A$1:A$50,SMALL(IF((Sheet1!$E$1:$E$50<>"")*(Sheet1!$F$1:$F$50="Running"),ROW(Sheet1!$A$1:$A$50),""),ROW($A1))))

It is an array formula, so commit with Ctrl-Shift-Enter,

Copy acroos 3 columns and down as far as you need.

 

[Bob Phillips]

Not, it is not sorted because you are extracting in the order in appears in
Sheet1.

--
__________________________________
HTH

Bob

Thank you Bob. This works great. One problem. I need to sort Sheet2,
col.C,
but it will not sort. Is this due to the: ROW($A...)? Is there any way
around? The data in Sheet2 are constantly changing and the data in col.C
should be sorted frequently.

 

[John]

I solved this by "cheating" the pivot table. I inserted a row-field to the
left (col.A) with a unique serial No. As a result the pivot table has no
longer got grouped data display. I also use VBA for automatic update and
sorting of the pivot table result. I'm OK with this for now. Thank you All !