Need help with formula

[Gubbelille]

Hi yall!
Im a beginner in this and cant get it to work properly.

So lets say in a1 i have a certain specific text that tells a tool number, in b1 i have 1 of 4 places it will be. In c1 i want to give it the right program based on these values. I can get it right with one but as we have 9 different tools designated to 4 different spots, it all depends on toolname and toolspot that give the number.

Something in the style like if(and(a1="tool1",b1="spot1";"50")
And can u add more after that, that will change spot from 1,2,3,4 and tool1,2,3 and so on and give different value in the end?

Hope this describes it right as english is my second längtade!

 

[Gubbelille]

The problem is all is good in the green field, untill i change the name from STD to LTH o get FALSE. Is it not possible to do this or am i missing something?

=IF(ISNUMBER(SEARCH("526 LHD STD",A5)),IF(C5="FH-049","54",IF(ISNUMBER(SEARCH("526 LHD STD",A5)),IF(C5="FH-050","53",IF(ISNUMBER(SEARCH("526 LHD STD",A5)),IF(C5="FH-052","44",IF(ISNUMBER(SEARCH("526 LHD STD",A5)),IF(C5="FH-051","46",IF(ISNUMBER(SEARCH("526 LHD LTH",A5)),IF(C5="FH-049","62"))))))))))

If i could use this formula it wont be any problem to give each and every tool its designated route to program as some are the same but with only different number in the end, therefor is and search for the first letters.

 

[Gubbelille]

This doesn't require the list to be sorted, or in the same order, but it does assume that the program is numeric:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2
3		Tool	Program	Place
4		Tool 1	54	FH-049		Tool	Program	Place		Tool	Program	Place		Tool	Program	Place		Tool	Program	Place
5						Tool 1	54	FH-049		Tool 1	53	FH-050		Tool 1	44	FH-052		Tool 1	46	FH-051
6						Tool 2	54	FH-049		Tool 2	53	FH-050		Tool 2	44	FH-052		Tool 2	46	FH-051
7						Tool 3	54	FH-049		Tool 3	53	FH-050		Tool 3	44	FH-052		Tool 3	46	FH-051
8						Tool 4	54	FH-049		Tool 4	53	FH-050		Tool 4	44	FH-052		Tool 4	46	FH-051
9						Tool 5	62	FH-049		Tool 5	49	FH-050		Tool 5	65	FH-052		Tool 5	61	FH-051
10						Tool 6	62	FH-049		Tool 6	49	FH-050		Tool 6	65	FH-052		Tool 6	61	FH-051
11						Tool 7	62	FH-049		Tool 7	49	FH-050		Tool 7	65	FH-052		Tool 7	61	FH-051
12						Tool 8	58	FH-049		Tool 8	60	FH-050		Tool 8	64	FH-052		Tool 8	57	FH-051
13						Tool 9	56	FH-049		Tool 9	63	FH-050		Tool 9	64	FH-052		Tool 9	55	FH-051
Sheet2

Cell Formulas
Range	Formula
C4	C4	=SUMIFS(G5:S13,F5:R13,B4,H5:T13,D4)

That one worked as intended!! Thanks alot all!!

 

[Eric W]

Glad we could help!