|
Posted to microsoft.public.excel.worksheet.functions
|
|
|
VLOOKUP MATCH INDEX two conditions / criterias text and date
The best way is to use a PIVOT table
"John" wrote:
In Sheet1 I have a table with 5000 rows and 30 columns. I have simplifiede it
he
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.
--
Thanks
John
|