Extract multiple results based on multiple criteria

T

tara657

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 Dat
=INDEX(Task_Table1!$K$2:$K$500,MATCH(Data!$A4,IF(Task_Table1!$E$2:$E$500=$B$1,Task_Table1!$D$2:$D$500),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
 
R

ryguy7272

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---
 
T

T. Valko

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.
 
T

tara657

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using CountIf based on AND criteria from two columns 3
Multiple criteria with multiple results in one cell 4
SUMIFS, one criteria range, multiple criteria 3
Multiple criteria search 2
return multiple rows of data based on criteria 7
With F1 value, look at A, if match or partial match return B 5
Multiple Criteria Sum 8
SUMPRODUCT and Dates 2

Top