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.

	Month 1	Month 2	Month 3	Month 4			Level 1	Level 2	Level 3	Level 4	Level 5
Project 1	1	1	1	2		SrCTM	0.1	0.2	0.3	0.3	0.2
Project 2	0	2	2	3		CTM	0.5	0.5	0.5	0.2	0.2
						CTA	1	1	1	1	0.5
	Month 1	Month 2	Month 3	Month 4
SrCTM
CTM
CTA

<colgroup><col width="64" style="width: 48pt;" span="12"> <tbody> </tbody>

 

[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.