Using IF, AND, OR formula

[Jewells0905]

Hello All,

I am trying to write a formula to make an interactive spreadsheet to select cars based on three criteria:

1. Origin. In cells B1:D1 the user enters one or more regions of interest, including "US", "Europe", and "Japan", not necessarily in this order, either all three or just some of them, one name per cell.
2. Maximum weight. In cell B2, the user enters the maximum allowed car weight in pounds.
3. Maximum number of cylinders. The user enters the maximum allowed number of cylinders in cell B3.

The three criteria must be met simultaneously for a car to be selected. If a car is selected, its name from column A should appear in the same row in column J.

Now my issue is that I am not able to get the formula to work for column J. I may just not have it in the correct order, or something simple, but I am needing to use IF, AND as well as OR for this formula. Below is what I have thus far.
=IF(AND(OR(B1,B2,B3),A7,)(I7=B1,C1,D1))

Origin(s):	US	Europe	Japan
Max weight:	3500
Max cylinders:	6
Total:	1
Car	MPG	Cylinders	Displacement	Horsepower	Weight	Acceleration	Model	Origin	Selection
Chevrolet Chevelle Malibu	18	8	307	130	3504	12	70	US	#N/A
Buick Skylark 320	15	8	350	165	3693	11.5	70	US
Plymouth Satellite	18	8	318	150	3436	11	70	US
AMC Rebel SST	16	8	304	150	3433	12	70	US
Ford Torino	17	8	302	140	3449	10.5	70	US
Ford Galaxie 500	15	8	429	198	4341	10	70	US
Chevrolet Impala	14	8	454	220	4354	9	70	US
Plymouth Fury iii	14	8	440	215	4312	8.5	70	US
Pontiac Catalina	14	8	455	225	4425	10	70	US
AMC Ambassador DPL	15	8	390	190	3850	8.5	70	US
Citroen DS-21 Pallas	0	4	133	115	3090	17.5	70	Europe
Chevrolet Chevelle Concours (sw)	0	8	350	165	4142	11.5	70	US
Ford Torino (sw)	0	8	351	153	4034	11	70	US
Plymouth Satellite (sw)	0	8	383	175	4166	10.5	70	US
AMC Rebel SST (sw)	0	8	360	175	3850	11	70	US
Dodge Challenger SE	15	8	383	170	3563	10	70	US
Plymouth 'Cuda 340	14	8	340	160	3609	8	70	US
Ford Mustang Boss 302	0	8	302	140	3353	8	70	US
Chevrolet Monte Carlo	15	8	400	150	3761	9.5	70	US
Buick Estate Wagon (sw)	14	8	455	225	3086	10	70	US
Toyota Corolla Mark ii	24	4	113	95	2372	15	70	Japan

Thank you

 

[dreid1011]

Perhaps this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Origin(s):	US	Europe	Japan
2	Max weight:	3500
3	Max cylinders:	6
4	Total:	1
5
6	Car	MPG	Cylinders	Displacement	Horsepower	Weight	Acceleration	Model	Origin	Selection
7	Chevrolet Chevelle Malibu	18	8	307	130	3504	12	70	US
8	Buick Skylark 320	15	8	350	165	3693	11.5	70	US
9	Plymouth Satellite	18	8	318	150	3436	11	70	US
10	AMC Rebel SST	16	8	304	150	3433	12	70	US
11	Ford Torino	17	8	302	140	3449	10.5	70	US
12	Ford Galaxie 500	15	8	429	198	4341	10	70	US
13	Chevrolet Impala	14	8	454	220	4354	9	70	US
14	Plymouth Fury iii	14	8	440	215	4312	8.5	70	US
15	Pontiac Catalina	14	8	455	225	4425	10	70	US
16	AMC Ambassador DPL	15	8	390	190	3850	8.5	70	US
17	Citroen DS-21 Pallas	0	4	133	115	3090	17.5	70	Europe	Citroen DS-21 Pallas
18	Chevrolet Chevelle Concours (sw)	0	8	350	165	4142	11.5	70	US
19	Ford Torino (sw)	0	8	351	153	4034	11	70	US
20	Plymouth Satellite (sw)	0	8	383	175	4166	10.5	70	US
21	AMC Rebel SST (sw)	0	8	360	175	3850	11	70	US
22	Dodge Challenger SE	15	8	383	170	3563	10	70	US
23	Plymouth 'Cuda 340	14	8	340	160	3609	8	70	US
24	Ford Mustang Boss 302	0	8	302	140	3353	8	70	US
25	Chevrolet Monte Carlo	15	8	400	150	3761	9.5	70	US
26	Buick Estate Wagon (sw)	14	8	455	225	3086	10	70	US
27	Toyota Corolla Mark ii	24	4	113	95	2372	15	70	Japan	Toyota Corolla Mark ii
Sheet4

Cell Formulas
Range	Formula
J7:J27	J7	=IF(AND(ISNUMBER(MATCH(I7,$B$1:$D$1,0)),F7<=$B$2,C7<=$B$3),A7,"")

 

[Jewells0905]

=IF(AND(ISNUMBER(MATCH(I7,$B$1:$D$1,0)),F7<=$B$2,C7<=$B$3),A7,"")

Hi, Thank you so much for your response, while that is not giving me an error, it's also not giving me the correct calculations. With the current info, it should be giving me 281 cars, but it's showing 406 cars that match that criteria. Also I have to use the IF, AND as well as OR for this. This is for a class I'm taking to learn data analysis, so it's required to use these 3 functions. I have been trying to figure this out for hours. I feel like I'm losing my mind over here! HAHA

 

[dreid1011]

Well, not trying to do your homework for you, but this solves the OR problem.. though I'm not sure what the other issue is. I tested on the data provided and as far as I can tell, it works as intended.

=IF(AND(OR(I7=$B$1,I7=$C$1,I7=$D$1),F7<=$B$2,C7<=$B$3),A7,"")

 

[Jewells0905]

Oh my gosh, you are a formula genius! Thank you so much I have been fussing with this for hours, and of course after seeing your solution, it makes perfect sense. Now I can move onto the next step. Thank you again for your help.

 

[dreid1011]

Oh my gosh, you are a formula genius! Thank you so much I have been fussing with this for hours, and of course after seeing your solution, it makes perfect sense. Now I can move onto the next step. Thank you again for your help.

You're welcome.