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!

 

[kweaver]

I would help if you could post sample data and results you want using XL2BB.

 

[Gubbelille]

Sure, i can show it tomorow as I dont have it available right now.

 

[Gubbelille]

123123.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3		Tool	Program	Place
4		Tool 1		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
14
15
16
Blad1

 

[Gubbelille]

123123.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3		Tool	Program	Place
4		Tool 1		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
14
15
16
Blad1

So basically i want what it says in tool and place determine what program it will be in C, is there any possible way to do this? Ive tried with if, and formula but somehow i just cant get it right. If i only do if it will be to long as it can only handle 64 as far as i could tell.

So lets say in A4=Tool 1 and D4=FH-049 then C4 returns the program 54. If A4=Tool 1 and D4=FH-050 then C4 will show 53 and so on.

 

[AhoyNC]

Maybe:

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	=INDEX($F$5:$T$13,MATCH($B$4,$F$5:$F$13,0),MATCH($D$4,$F$5:$T$5,0)-1)

 

[kweaver]

Can one assume that the Tools are sorted ascending and Places are always the same in the respective columns? If not, I don't think the nice solution from AhoyNC will work.

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	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 22	54	FH-049		Tool 22	53	FH-050		Tool 22	44	FH-052		Tool 22	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 2	54	FH-049		Tool 2	53	FH-048		Tool 2	44	FH-052		Tool 2	46	FH-050
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-061
Sheet4

 

[Gubbelille]

Can one assume that the Tools are sorted ascending and Places are always the same in the respective columns? If not, I don't think the nice solution from AhoyNC will work.

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	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 22	54	FH-049		Tool 22	53	FH-050		Tool 22	44	FH-052		Tool 22	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 2	54	FH-049		Tool 2	53	FH-048		Tool 2	44	FH-052		Tool 2	46	FH-050
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-061
Sheet4

Well the list will be the same, the table will have different tools on different places.

 

[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"))))))))))

 

[Eric W]

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)