Hi,

I've got a spreadsheet with data exported for a project plan. In order to
create some Excel charts I need to extract dates based on multiple criteria.
Unfortunately, in some cases the criteria are not unique and I need all the
results. I'm unsing INDEX and MATCH to get my data but I can't figure out how
to convert into something where I can get the multiple results.

I'm getting data from sheet Task_Table1 where column D is the part number
and column E is the task. For some part numbers there are occasionally
repeated tasks
The following array formula gives me the date I need to extract for the
first match for each item in my list part numbers on worksheet Data
=INDEX(Task_Table1!$K$2:$K$500,MATCH(Data!$A4,IF(T ask_Table1!$E$2:$E$500=$B$1,Task_Table1!$D$2:$D$50 0),0))

Is there a way to modify this such that if my list of part numbers
(repeating the part number if two of the particular tasks are expected) can
have the required dates listed in another column? The formula can display an
error or NA if there's no result.

TIA

Sounds like a job for the alternative-vlookup (that's what I call it):
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")

Assume ColumnA is: a, b, c, a, b, c, etc...
Assume ColumnB is: 1, 2, 3, 4, 5, 6, etc...

put a 'c' in E1 and the function in F1; fill down.

Does that help?

Regards,
Ryan---
--
RyGuy


"tara657" wrote:

Hi,

I've got a spreadsheet with data exported for a project plan. In order to
create some Excel charts I need to extract dates based on multiple criteria.
Unfortunately, in some cases the criteria are not unique and I need all the
results. I'm unsing INDEX and MATCH to get my data but I can't figure out how
to convert into something where I can get the multiple results.

I'm getting data from sheet Task_Table1 where column D is the part number
and column E is the task. For some part numbers there are occasionally
repeated tasks
The following array formula gives me the date I need to extract for the
first match for each item in my list part numbers on worksheet Data
=INDEX(Task_Table1!$K$2:$K$500,MATCH(Data!$A4,IF(T ask_Table1!$E$2:$E$500=$B$1,Task_Table1!$D$2:$D$50 0),0))

Is there a way to modify this such that if my list of part numbers
(repeating the part number if two of the particular tasks are expected) can
have the required dates listed in another column? The formula can display an
error or NA if there's no result.

TIA

If I understand your post, you can use the same basic technique but change
the IF logical test to:

NOT(ISNA(AB1:AB100))

You'd have to come up with a different error trap, also.

=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1)

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
Sounds like a job for the alternative-vlookup (that's what I call it):
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")

Assume ColumnA is: a, b, c, a, b, c, etc...
Assume ColumnB is: 1, 2, 3, 4, 5, 6, etc...

put a 'c' in E1 and the function in F1; fill down.

Does that help?

Regards,
Ryan---
--
RyGuy


"tara657" wrote:

Hi,

I've got a spreadsheet with data exported for a project plan. In order to
create some Excel charts I need to extract dates based on multiple
criteria.
Unfortunately, in some cases the criteria are not unique and I need all
the
results. I'm unsing INDEX and MATCH to get my data but I can't figure out
how
to convert into something where I can get the multiple results.

I'm getting data from sheet Task_Table1 where column D is the part number
and column E is the task. For some part numbers there are occasionally
repeated tasks
The following array formula gives me the date I need to extract for the
first match for each item in my list part numbers on worksheet Data
=INDEX(Task_Table1!$K$2:$K$500,MATCH(Data!$A4,IF(T ask_Table1!$E$2:$E$500=$B$1,Task_Table1!$D$2:$D$50 0),0))

Is there a way to modify this such that if my list of part numbers
(repeating the part number if two of the particular tasks are expected)
can
have the required dates listed in another column? The formula can display
an
error or NA if there's no result.

TIA

Thanks,

This didn't work quite the way I'm looking for.

Here's an example of what I want to do - I suspect my test explanation was
confusing.
Columns A, B, C are my data. E and F are my criteria. G is what I want to find
A B C D E F G
a 1 z a 1 z
b 2 y c 3 x
c 3 x c 3 r
d 4 w e 5 v
e 5 v
f 1 t
g 2 s
c 3 r
i 4 q

I'm now leaning towards insisting on unique data in column A.

Thanks again.

"T. Valko" wrote:

If I understand your post, you can use the same basic technique but change
the IF logical test to:

NOT(ISNA(AB1:AB100))

You'd have to come up with a different error trap, also.

=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1)

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
Sounds like a job for the alternative-vlookup (that's what I call it):
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($ B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")

Assume ColumnA is: a, b, c, a, b, c, etc...
Assume ColumnB is: 1, 2, 3, 4, 5, 6, etc...

put a 'c' in E1 and the function in F1; fill down.

Does that help?

Regards,
Ryan---
--
RyGuy


"tara657" wrote:

Hi,

I've got a spreadsheet with data exported for a project plan. In order to
create some Excel charts I need to extract dates based on multiple
criteria.
Unfortunately, in some cases the criteria are not unique and I need all
the
results. I'm unsing INDEX and MATCH to get my data but I can't figure out
how
to convert into something where I can get the multiple results.

I'm getting data from sheet Task_Table1 where column D is the part number
and column E is the task. For some part numbers there are occasionally
repeated tasks
The following array formula gives me the date I need to extract for the
first match for each item in my list part numbers on worksheet Data
=INDEX(Task_Table1!$K$2:$K$500,MATCH(Data!$A4,IF(T ask_Table1!$E$2:$E$500=$B$1,Task_Table1!$D$2:$D$50 0),0))

Is there a way to modify this such that if my list of part numbers
(repeating the part number if two of the particular tasks are expected)
can
have the required dates listed in another column? The formula can display
an
error or NA if there's no result.

TIA

Excel 2007
Tables, Countifs, Sumifs
http://www.mediafire.com/file/fqminw...01_23_09a.xlsx