IF AND OR Function

[Roadrash]

Hi,

I need to return a value in a cell based on 2 pieces of information/criteria being true.

I have 3 job roles

SrCTM
CTM
CTA

I then have 5 possible values 1,2,3,4 or 5 in a second cell.

I need the result in the final cell to be dependent on if (A1= Sr CTM and C2=1,2,3 etc) then it returns a value specified in another cell e.g. F2 If false then it needs to look for A1=CTM and C2 =1,2,3 etc then returns value in G2.

Using logic steps should be possible however the formula could be very long unless there is a short version. (appears Nested IFs should work)

Thanks.

 

[Roadrash]

OK seems borders work.

I want to fill the table with the yellow cell.

So if the first cell next to the yellow cell = SrCTM and project 1 month 1 = 1 then return SrCTM Level 1 = 0.1, if project 1 month 1 = 2 then returns 0.2.

For each cell I need the formula to determine if the role = SrCTM, CTM or CTA and then look at the corresponding month and whether it is level 1-5 and return the correct value according to the true statements.

[TABLE="width: 576"]
<colgroup><col width="64" style="width: 48pt;" span="12"> <tbody>[TR]
[TD="class: xl68, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Month 1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Month 2[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Month 3[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Month 4[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl71, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Level 1[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Level 2 [/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Level 3[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Level 4[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]Level 5[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]Project 1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]SrCTM[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.3[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0.2[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]Project 2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]CTM[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0.2[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0.2[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]CTA[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]0.5[/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl75, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]Month 1[/TD]
[TD="class: xl66, bgcolor: transparent"]Month 2[/TD]
[TD="class: xl66, bgcolor: transparent"]Month 3[/TD]
[TD="class: xl66, bgcolor: transparent"]Month 4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]SrCTM[/TD]
[TD="class: xl67, bgcolor: yellow"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]CTM[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl77, bgcolor: transparent"]CTA[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

You didn't explain the significance of "Project 1", "Project 2" etc. so I put a value in A7 which allows you to specify the project:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Month 1	Month 2	Month 3	Month 4			Level 1	Level 2	Level 3	Level 4	Level 5
2	Project 1	1	1	1	2		SrCTM	0.1	0.2	0.3	0.3	0.2
3	Project 2	0	2	2	3		CTM	0.5	0.5	0.5	0.2	0.2
4							CTA	1	1	1	1	0.5
5
6
7	Project 1	Month 1	Month 2	Month 3	Month 4
8	SrCTM	0.1	0.1	0.1	0.2
9	CTM	0.5	0.5	0.5	0.5
10	CTA	1	1	1	1
Sheet1

Cell Formulas
Range	Formula
B8	=IFERROR(INDEX($H$2:$L$4,MATCH($A8,$G$2:$G$4,0),INDEX($A$1:$E$3,MATCH($A$7,$A$1:$A$3,0),MATCH(B$7,$A$1:$E$1,0))),0)

Paste across and down.

WBD

 

[Roadrash]

Thanks. I was going to set up a separate table for each project (1, 2) etc with SrCTM, CTM etc in each project table. Then use the formula to fill each separate table. I will give the formula a try on my data set.

Thanks again.