Search row for 4 criteria - return row if all criteria is met

[Doodle24]

Hello All,

I have tried to formulate something that searches through rows to meet 4 criterias. If all criteria is met I need the row number or the entire row returned, if not continue the search. Half of my criteria is straight forward BLUE=BLUE, GREEN=GREEN while the other two needs to be within a range. For example, 1 day +- from a specific date in a cell or +-500 from a specific number in a cell. I usually use if loops, vlookup and index/match but it seems I have to get into VBA with this.. Any suggestions? Thanks so much for any ideas on how to tackle this!

 

[StephenCrump]

Welcome to the Forum!

With Excel 365, you could use the FILTER function to return all rows meeting your criteria:

	A	B	C	D	E	F	G	H
1						Find
2		Test1	Test2	Test3		Test1	Blue
3		Blue	Green	6		Test2	Green
4		Yellow	Orange	9		Test 3	Min	5
5		Yellow	Orange	23			Max	10
6		Blue	Orange	-1
7		Blue	Green	2		Results
8		Yellow	Yelllow	6		Blue	Green	6
9		Blue	Green	7		Blue	Green	7
10		Red	Yelllow	8
11
Sheet1

Cell Formulas
Range	Formula
F8:H9	F8	=FILTER(B3:D10,(B3:B10=G2)*(C3:C10=G3)*(D3:D10>=H4)*(D3:D10<=H5))
Dynamic array formulas.

 

[Doodle24]

Thank you so much for the reply!!

I did try it out but I get the number value# error. I read that the data rows must equal the argument row.. I'm trying to bring in a row with additional cells (around 10-15 cells) from my 4 criteria cells- can that be the issue? Do you know any workarounds? Thanks

 

[Doodle24]

Welcome to the Forum!

With Excel 365, you could use the FILTER function to return all rows meeting your criteria:

	A	B	C	D	E	F	G	H
1						Find
2		Test1	Test2	Test3		Test1	Blue
3		Blue	Green	6		Test2	Green
4		Yellow	Orange	9		Test 3	Min	5
5		Yellow	Orange	23			Max	10
6		Blue	Orange	-1
7		Blue	Green	2		Results
8		Yellow	Yelllow	6		Blue	Green	6
9		Blue	Green	7		Blue	Green	7
10		Red	Yelllow	8
11
Sheet1

Cell Formulas
Range	Formula
F8:H9	F8	=FILTER(B3:D10,(B3:B10=G2)*(C3:C10=G3)*(D3:D10>=H4)*(D3:D10<=H5))
Dynamic array formulas.

Thank you so much for the reply!!

I did try it out but I get the number value# error. I read that the data rows must equal the argument row.. I'm trying to bring in a row with additional cells (around 10-15 cells) from my 4 criteria cells- can that be the issue? Do you know any workarounds? Thanks

 

[StephenCrump]

Can you please post a sample of your data, so we can better understand what you're trying to do.

It's best if you can do this using the XL2BB add-in: XL2BB - Excel Range to BBCode so that we can copy directly into a test workbook.

 

[Doodle24]

Sorry for not stating my problem clearly. I'll get XL2BB in the future but got some constraints on my computer right now so I took a snippet of what I'm trying to accomplish.

I have 2 data sets with data that needs to be compared and additional data (data that won't be compared but we still need to keep it). I need to pair the rows based on the 4 criteria. They are color coded. If the 4 criteria match - I would need to bring in the row matches and pair it to the other. I got thousands of rows in my original sheets and most of the rows won't match each other and will be discarded.

Any suggestions would be much appreciated!

 

[StephenCrump]

Will your datasets always pair so neatly?

I'd start the analysis along these lines:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1									Possible
2		Cost	ID	Date	Length	Colour1	Colour2	Location	Matches?
3		695	1234	13 May 2022	6820	Green	Violet	NY	ABCD	EFGH
4		Blah	Blah	Blah	Blah	Blah	Blah	Blah	n/a
5
6												Possible
7		MinDate	Date	MaxDate	MinL	L	MaxL	Colour1	Colour2	ID	Name	Matches?
8		1 May 2022		31 May 2022				Green	Violet	ABCD		1234
9		Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	n/a
10		5 May 2022		17 May 2022				Green	Violet	EFGH		1234
Sheet1

Cell Formulas
Range	Formula
I3:J3,I4	I3	=TRANSPOSE(FILTER(J$8:J$10,(H$8:H$10=F3)*(I$8:I$10=G3)*(B$8:B$10<=D3)*(D$8:D$10>=D3),"n/a"))
L8:L10	L8	=TRANSPOSE(FILTER(C$3:C$4,(F$3:F$4=H8)*(G$3:G$4=I8)*(D$3:D$4>=B8)*(D$3:D$4<=D8),"n/a"))
Dynamic array formulas.

 

[Doodle24]

Will your datasets always pair so neatly?

I'd start the analysis along these lines:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1									Possible
2		Cost	ID	Date	Length	Colour1	Colour2	Location	Matches?
3		695	1234	13 May 2022	6820	Green	Violet	NY	ABCD	EFGH
4		Blah	Blah	Blah	Blah	Blah	Blah	Blah	n/a
5
6												Possible
7		MinDate	Date	MaxDate	MinL	L	MaxL	Colour1	Colour2	ID	Name	Matches?
8		1 May 2022		31 May 2022				Green	Violet	ABCD		1234
9		Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	n/a
10		5 May 2022		17 May 2022				Green	Violet	EFGH		1234
Sheet1

Cell Formulas
Range	Formula
I3:J3,I4	I3	=TRANSPOSE(FILTER(J$8:J$10,(H$8:H$10=F3)*(I$8:I$10=G3)*(B$8:B$10<=D3)*(D$8:D$10>=D3),"n/a"))
L8:L10	L8	=TRANSPOSE(FILTER(C$3:C$4,(F$3:F$4=H8)*(G$3:G$4=I8)*(D$3:D$4>=B8)*(D$3:D$4<=D8),"n/a"))
Dynamic array formulas.

Thanks Stephen! I'll give the formula a try in a bit. No, my data does not pair that nicely.. I got thousands of rows in one sheet with "additional" data that I won't use but the other data sheet is complete and needs to be matched. I wanted to show the logic behind what I'm trying to accomplish.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Search dataset rows for multiple criteria and combine rows if multiple criteria is met
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Doodle24]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Search dataset rows for multiple criteria and combine rows if multiple criteria is met
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thanks, I'll do that in the future! Should have read the guidelines closer.