index and match

  • Thread starter Thread starter cpliu
  • Start date Start date
C

cpliu

Example:
date project name stage
=============================
1/2/2010 project 1 development
1/4/2010 project 2 design
1/22/2010 project 1 test
2/1/2010 project 1 production

Using the example above, I'd like to pick up the first (and the last
if possible) occurance in Date of project 1 in test and production.

Use index formula below, I can only match one criterion but not 1.
=INDEX($A$2:$A$2045,MATCH(A8,$B$2:$B$2045,0))
A8 is project 1

How can add C column into the mix?

Thanks for the help,
 
Try these ARRAY formula

=IF(MAX(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))=0,"",INDEX($A$2:$A$20,MAX(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))-1))

=IF(MIN(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))=0,"",INDEX($A$2:$A$20,MIN(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))-1))

HTH

Bob
 
try this ARRAY formula that must be entered using ctrl+shift+enter
=MAX(IF((B2:B5="project1"),A2:A5))
 
Example:
date      project name  stage
=============================
1/2/2010        project 1       development
1/4/2010        project 2       design
1/22/2010       project 1       test
2/1/2010        project 1       production
Click to expand...

I'm trying to find the first date project 1 goes to test which is
1/22/2010, and it goes to proudction on 2/1/2010.
If there are mutliple ones, I'd like to find the last one to see how
long between the 2.
=MAX(IF((B2:B5="project1"),A2:A5))
Click to expand...
Thanks for the suggestion, but this does not take C column into
consideration.
=IF(MAX(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))=0,"",INDEX­($A$2:$A$20,MAX(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))-1)­)
=IF(MIN(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))=0,"",INDEX­($A$2:$A$20,MIN(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))-1)­)
Click to expand...

Thanks for the formula. Can you elaborate more? I'm not sure which one
is for which.

Thanks,
 
=IF(MAX(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))=0,"",INDEX­­($A$2:$A$20,MAX(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))-1­)­)
When trying the formula above, it gave an error message and
highlighted INDEX.

I used A = date, B = project, C = milestone, G1 = project 1, G2 =
test.

Anything missing?

Thanks,
 
=MAX(IF(($B$2:$B$5="project1")*(C2:C5="test"),$A$2:$A$5))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Example:
date � � �project name �stage
=============================
1/2/2010 � � � �project 1 � � � development
1/4/2010 � � � �project 2 � � � design
1/22/2010 � � � project 1 � � � test
2/1/2010 � � � �project 1 � � � production
Click to expand...

I'm trying to find the first date project 1 goes to test which is
1/22/2010, and it goes to proudction on 2/1/2010.
If there are mutliple ones, I'd like to find the last one to see how
long between the 2.
=MAX(IF((B2:B5="project1"),A2:A5))
Click to expand...
Thanks for the suggestion, but this does not take C column into
consideration.
=IF(MAX(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))=0,"",INDEX�($A$2:$A$20,MAX(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))-1)�)
=IF(MIN(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))=0,"",INDEX�($A$2:$A$20,MIN(IF(($B$2:$B$20=$G$1)*($C$2:$C$20=$G$2),ROW($A$2:$A$20)))-1)�)
Click to expand...

Thanks for the formula. Can you elaborate more? I'm not sure which one
is for which.

Thanks,
 
=MAX(IF(($B$2:$B$5="project1")*(C2:C5="test"),$A$2:$A$5))
Click to expand...

Thank you. It works with my example but comes up with 1/0/00 on real
data. Does the data need to meet certain requirements for it to work?

Thanks again for your help,
 
1/0/00 is a value of 0 that's formatted as a date.

So look at your real data. Are the values in column A really dates--or just
text that look like dates.

You can use:

=count(a2:a5)
to count the number of numeric entries in a2:a5 (and dates are just numbers
nicely formatted in excel).

Compare that with:
=counta(a2:a5)
This counts the number of non-empty cells you have in that range.

If the results don't match, then there are some entries that are not really
dates. You'll want to convert them to real dates.
 
=count(a2:a5)
to count the number of numeric entries in a2:a5 (and dates are just numbers
nicely formatted in excel).

Compare that with:
=counta(a2:a5)
This counts the number of non-empty cells you have in that range.
Click to expand...

Thank you for the tip. It works now!!
 
Another idea:

Assumed: Columns of: dates,projects and status designations headed by headings of same names.
Each project appears with the oldest date and status first and with a last entry for the project with the second critical date.

Copy your column of data and headings twice.
Table 1 is the original data
Table 2 should be sorted by project (paste values
on Paste)
remove duplicates. Leaves first dates etc.
Table 3 sort by project.
sort by:newest to oldest
remove duplicates
Leaves last date etc.
Copy Table 3 to the end of Table 2.

Best wishes

Gabor Sebo
 
To retain stage of test and production by projects and dates:

Sort table by stage
delete all but test and production
sort oldest/newest
sort by project.

Best Regards

Gabor Sebo
 
Back
Top