Index/Match with two criteria to return past first instance

[Mattman121]

I am working with a sheet where I am doing an Index/Match where I pull data from a master sheet onto a separate sheet if it meets the two criteria it needs too. I know the Index/Match will only return the first instance but I read online that you can add count function to a column in the sheet and use that to modify the Index/Match formula to obtain however many results there are.

Right now I have it working by using to separates that accompany the master, the first pulls through the first criteria and then the second pulls the second criteria. I thought it would be easier and overall cleaner to be able to just pull to one sheet.

=INDEX('All in One'!$A$2:$I$660,MATCH(1,('All in One'!$D$2:$D$660>0)*('All in One'!$F$2:$F$660=0),0),1)

This is what i have for the two criteria.

Any input is greatly appreciated!

Thanks,

Matt

 

[XLearner]

Hi,

Similar to this one. In your case, assuming the following setup where columns B & C correspond to your D and F and the info you want to return when there's a match seats in column A:

	A	B	C	D	E
1	To return if Match	Val 1	Val 2		Results
2	A	0	0		B
3	B	4	0		G
4	C	1	1		J
5	D	0	1		M
6	E	0	1		R
7	F	0	1
8	G	1	0
9	H	0	1
10	I	4	1
11	J	4	0
12	K	0	0
13	L	0	1
14	M	3	0
15	N	4	1
16	O	3	1
17	P	4	1
18	Q	1	1
19	R	1	0
20	S	1	1
21	T	1	1

<tbody>
</tbody>

in E2:

=IF(ROW(1:1)>SUMPRODUCT(--($B$2:$B$21>0),--($C$2:$C$21=0)),"",
INDEX($A$2:$A$21,MATCH(SMALL(IF(($B$2:$B$21>0)*($C$2:$C$21=0),ROW($A$2:$A$21)),ROW(1:1)),ROW($A$2:$A$21),0),1))

validate with Ctrl+Shift+Enter (array formula) and copy down as necessary

Regards
XLearner

 

[XLearner]

Forgot to mention: in the first part of the formula SUMPRODUCT(--($B$2:$B$21>0),--($C$2:$C$21=0)) can be replaced with COUNTIFS($B$2:$B$21,">"&0, $C$2:$C$21,0) if you prefer

Regards
XLearner

 

[Mattman121]

That worked perfectly, thank you so much!

On somewhat of a side note, would it be possible to have that search through all the sheets in my workbook. I have been having problems linking all the data to the "master" sheet so going directly through each sheet would probably work better!

Thanks again!

Matt

 

[XLearner]

Hi,
Counting, Suming...accross worksheets is no problem although not straightforward. Returning several matches accross worksheets is also doable (i.e. Vlookup across multiple sheets in excel) when we know what we're looking for (Pen in the article) but that's not your case.

Not sure what you expect is doable but don't take this as a definitive answer as they are people with much more knowledge than mine...

Regards
XLearner

 

[Mattman121]

Hmmm, looks like i will do some more research.

Thanks again for all your help!

Matt