Shorten IF formula with multiple conditions and =If(or() for second part

[PuntingJawa]

I'm trying to shorten my formula if possible for a manual load planner. I have 2 types of formula that I am trying to modify.
1. =If() I am trying to shorten if possible for "Basic setup 1st row" and "3rd row" area.
2. =If(or() I haven't quite figure out how to accomplish for the "Basic setup 2nd row" area.

For point 1 I have the following formula.
=IF(C3="","",IF(C3="Box",42,IF(C3="Car kit",44,IF(C3="DO",65,IF(C3="edo",70)))))
I am missing the "LE" part in this but would be easy enough to add with "IF(C3="LE",48)" if making longer, but I am hoping a helper cell would cover the "LE" issue.

I could use the following for helper cells if needed
T17 "Box" X17 for return value (42)
T18 "Car Kit" X18 for return value (44)
T19 "DO" X19 for return value (65)
T20 "EDO" X20 for return value (70)
T21 "LE" X21 for return value (48)

For point 2 I keep coming up with a "Too many arguments" error when I attempt the "=IF(OR()" formula.
An example of what I am trying to do
If the formula is in H3 it would look to C3 and E3. If either has "Box" it would return value from B3.
If the formula is in L3 it would look to C3 and E3. If either has "Box" it would return value from F3.
In both instances, if C3 and E3 doesn't have "Box" follow Point 1 formula after.

Please let me know if my explanation does make sense. What I have works with experience in what I am working with to me, but I want to make it easier to use and modify.

R211 LOAD TEMPLATE.xlsx
	B	C	D	E	F	G	H	I	J	K	L
2	1st	Basic setup 1st row			1ST		2nd	Basic setup 2nd row			2ND
3	42	BOX	1	DO	65		42		1
4	65	DO	2	BOX	42				2		42
5	42	BOX	3	DO	65		42		3
6	65	DO	4	BOX	42				4		42
7	42	BOX	5	DO	65		42		5
8	65	DO	6	DO	65				6
9	65	DO	7	DO	65				7
10	65	DO	8	DO	65				8
11	65	DO	9	EDO	70				9
12			10						10
13			11						11
14			12						12
15			13						13
16			14						14
17			15						15
18	516	Total	Good	Total	544		126	Total	Good	Total	84
19	120	Left		Left	92		510	Left		Left	552
Visual

Cell Formulas
Range	Formula
D18,J18	D18	=IF(B19>=0,"Good","Remove")
B3:B17	B3	=IF(C3="","",IF(C3="Box",42,IF(C3="Car kit",44,IF(C3="DO",65,IF(C3="edo",70)))))
B18,L18,H18,F18	B18	=SUM(B3:B17)
B19,L19,H19,F19	B19	=636-B18
F3:F17	F3	=IF(E3="","",IF(E3="Box",42,IF(E3="Car kit",44,IF(E3="DO",65,IF(E3="edo",70)))))
H3:H17	H3	=IF(I3="Box",42,IF(I3="Car kit",44,IF(I3="DO",65,IF(I3="edo",70,IF(C3="box",42,"")))))
L3:L17	L3	=IF(K3="Box",42,IF(K3="Car kit",44,IF(K3="DO",65,IF(K3="edo",70,IF(E3="box",42,"")))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F19,H19,L19,N19	Cell Value	<0	text	NO
F19,H19,L19,N19	Cell Value	>0	text	NO
B18:B19	Cell Value	<0	text	NO
B19	Cell Value	>0	text	NO

 

[Fluff]

For point 1 try

=IF(C3="","",INDEX($X$17:$X$21,MATCH(C3,$T$17:$T$21,0)))

 

[Fluff]

For point 2

=IF(OR(C3="box",E3="box"),B3,IF(C3="","",INDEX($X$17:$X$21,MATCH(C3,$T$17:$T$21,0))))

 

[PuntingJawa]

Point 1 is good.
Point 2 has an issue.
If C3 or E3 does not contain "Box" it should look to I3 for the phrase. If I3 is blank it should return no value.

 

[Fluff]

But you said

In both instances, if C3 and E3 doesn't have "Box" follow Point 1 formula after.

Which is what the formula does.

 

[PuntingJawa]

I figured it out with what you had given from point 1.

What you provided was:
=IF(OR(C3="box",E3="box"),B3,IF(C3="","",INDEX($X$17:$X$21,MATCH(C3,$T$17:$T$21,0))))

I changed it to the following by combing your 1st point formula with the =if(or() that you provided with the 2nd point.
=IF(OR(C3="box",E3="box"),B3,IF(I3="","",INDEX($X$17:$X$21,MATCH(I3,$T$17:$T$21,0))))

I know where the error was. It was in my explanation. 2nd row was essentially mirroring the values from 1st row setup when it should have been looking for what was in the 2nd row setup after checking if "BOX" value was present first.

As always, you're amazing, Fluff. Thank you very much!

This opens a lot of room for expansion and doesn't limit me to a static setup. It by far improved on what I had thrown together. Thank you so very much! This forum has helped me with so many sheets that have made my already chaotic day easier.

 

[Fluff]

Glad to help & thanks for the feedback.