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.

 

[Jonmo1]

Try

=IF(OR(C2=1,C2=2,C2=3,C2=4,C2=5),IF(A1="SrCTM",F2,IF(A1="CTM",G2,IF(A1="CTA",H2,"A1 is neither"))),"C2 is not 1,2,3,4 or 5")

 

[Roadrash]

Thanks.

Looks like it will work however C2 values of 1,2,3,4 and 5 have different values I want to pull in from another cell. So if answer is C2=1 then use G2, if answer to C2=2 then use G3. In the formula I think it assumes if C2= either 1,2,3,4 or 5 then it returns G2, rather than 5 possibilities.

Apologies my original question, example was badly worded.

thanks.

 

[Jonmo1]

Your description made it appear that it was the value of A1 that determined if it was F2 G2 or H2
The C2 = 1 2 3 4 or 5 was merely a true or false test before even checking A1

Can you post desired results for all possible combinations of the 2 cells ?

 

[Roadrash]

3 Roles, Sr CTM, CTM, CTA
5 time periods (1-5) and each time period has a resource value which is dependent on the role.

If

A1= SrCTM
and
C1=1
then
use value in associated with Sr CTM and 1 which will be in another table
G2
If C1=2
use value in
G3
etc with C=5 using G5

if first check is false and it is not Sr CTM then needs to check if value in A1=CTM
If true then
C1=1
Use value associated with CTM and 1 which will be in table e.g. F1
C1=2 then use F2

Again if the first value is false, needs to check for CTA and then pull in the value associated with C1= 1, 2, 3 etc. which will be in separate table using G1 etc.

Problem I am finding is the multiple logical arguments.

Thanks.

 

[wideboydixon]

Is it this?

Book1
	A	B	C	D	E	F	G
1	Sr CTM		1	k	a	f	k
2	CTM		3	h	b	g	l
3	CTA		4	d	c	h	m
4					d	i	n
5					e	j	o
Sheet1

Cell Formulas
Range	Formula
D1	=INDEX($E$1:$G$5,$C1,MATCH($A1,{"CTA","CTM","Sr CTM"},0))

Just extrapolating your list and using a fair amount of guessing!

WBD

 

[Roadrash]

The data are in separate tables.
Month 1, Month 2, Month 3
SrCTM
CTM
CTA
are in one table

the 1-5 time periods are in a second table
Month 1, Month 2, Month 3
Study 2 2 3

dependent on the result of role + time period then the value posted comes from a

third table of role versus time period.
Period 1, Period 2, Period 3 etc
SrCTM 0.5 0.6 0.2
CTM 1 1 1
CTA 0.5 0.5 1

Does not let me post the tables here as removes formatting.

 

[wideboydixon]

Sorry but I can't decipher your tables; you'll need to post proper tables :-|

WBD

 

[Roadrash]

How do I post tables without it removing the formatting.

Thanks.

 

[Jonmo1]

Try the MrExcelHTMLMaker addon.
There's a link to it in Post number 31 in this thread
https://www.mrexcel.com/forum/about-board/1017050-mrexcel-html-maker-update-4.html