List any data on a row where some columns match with multiple criterias

[scharpentier]

I'm looking to get the row number of every occurance of a given value, be it occuring once or 100 times.

My data looks like this:

Please Login or Register  to view this content.

My final goal is to retreive full row of every time A and B match my criterias.

For exemple, I could want to get all the rows where A="Apples" and B="Grocery Store". The thing is C might not be unique, so I can't base myself on C to determine if the row was listed previously.

The solution I tought of was to list the row numbers wich have to be unique.

So my filtered table would give me this:

Please Login or Register  to view this content.

I know exactly how to use INDEX() to get B, C and D. My issue is how to make the formula for A.

I got this:

Please Login or Register  to view this content.

but it will just repeatedly spit the first row number matching the conditions over and over, and that's where I'm stuck.

I need your help to complete this MATCH() formula to not repeat the previous outputs.

Thank you very much!

[shank_mis]

Hey,

Assuming your data is in range A1:C6:

In Cell D1 Write Formula: =INDEX($A$1:$A$6,SMALL(IF(($A$1:$A$6="Apples")*($B$1:$B$6="Grocery Store"),ROW($A$1:$A$6)),ROW(A1)))
In Cell E1 Write Formula: =INDEX($B$1:$B$6,SMALL(IF(($A$1:$A$6="Apples")*($B$1:$B$6="Grocery Store"),ROW($A$1:$A$6)),ROW(A1)))
In Cell F1 Write Formula: =INDEX($C$1:$C$6,SMALL(IF(($A$1:$A$6="Apples")*($B$1:$B$6="Grocery Store"),ROW($A$1:$A$6)),ROW(A1)))

Confirm with Ctrl+Shift+Enter

Select the formulae and drag down. Formula would return #N/A when conditions won't match. You can use IFERROR() for error handling.

---

Shashank

[FlameRetired]

Similar but with one formula. In A10 filled down and across.

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1		A	B	C		Criteria
2	1	Apples	Grocery Store	20		Apples	Grocery Store
3	2	Oranges	Grocery Store	12
4	3	Apples	Grocery Store	20
5	4	Oranges	Neighbour	2
6	5	Oranges	Grocery Store	31
7	6	Apples	Neighbour	5
8
9
10	1	Apples	Grocery Store	20
11	3	Apples	Grocery Store	20
12

[scharpentier]

Similar but with one formula. In A10 filled down and across.

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1		A	B	C		Criteria
2	1	Apples	Grocery Store	20		Apples	Grocery Store
3	2	Oranges	Grocery Store	12
4	3	Apples	Grocery Store	20
5	4	Oranges	Neighbour	2
6	5	Oranges	Grocery Store	31
7	6	Apples	Neighbour	5
8
9
10	1	Apples	Grocery Store	20
11	3	Apples	Grocery Store	20
12

This is not right because of your data table, it should not have numbers in The A column, only in the filtered table. Same for the row 1, the letters represents the column headers, they weren’t part of the data.

[scharpentier]

The data table looks like this:

	A	B	C
2	Apples	Grocery Store	20
3	Oranges	Grocery Store	12
4	Apples	Grocery Store	20
5	Oranges	Neighbour	2
6	Oranges	Grocery Store	31
7	Apples	Neighbour	5

And the expected resulting table looks like this (in a different sheet or different column/row):

	A	B	C	D	E	F	G
2			Criteria		Apples	Grocery Store
3
4	1	Apples	Grocery Store	20
5	3	Apples	Grocery Store	20

[FlameRetired]

In Sheet1

	A	B	C
1	Apples	Grocery Store	20
2	Oranges	Grocery Store	12
3	Apples	Grocery Store	20
4	Oranges	Neighbour	2
5	Oranges	Grocery Store	31
6	Apples	Neighbour	5

In Sheet2

	A	B	C	D	E	F
1			Criteria		Apples	Grocery Store
2	1	Apples	Grocery Store	20
3	3	Apples	Grocery Store	20
4

The formula in Sheet2 A2 and filled down until you get blanks returns row numbers.

Please Login or Register  to view this content.

In B2 filled down and across returns the fruits and source(s).

Please Login or Register  to view this content.

[FlameRetired]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.