Please help fix formula syntax!

[TWienholz]

Hello. I am trying to create a formula that looks for a particular question code in a specific cell (eg, A1, A2, B1, etc) and populates the question text in another cell. I thought an If(OR formula would work, but either my syntax is wrong or maybe I'm going about it the wrong way. Can someone please help? This is what I have so far...

=IF(AND(OR(E2="A1",'Mock CAHPS Adult CQs'!C2,OR(E2="A2",'Mock CAHPS Adult CQs'!C3,OR(E2="B1",'Mock CAHPS Adult CQs'!C4,OR(E2="B2",'Mock CAHPS Adult CQs'!C5,OR(E2="B3",'Mock CAHPS Adult CQs'!C6,OR(E2="B4",'Mock CAHPS Adult CQs'!C7,OR(E2="B5",'Mock CAHPS Adult CQs'!C8,OR(E2="C1",'Mock CAHPS Adult CQs'!C9),OR(E2="D1",'Mock CAHPS Adult CQs'!C10,OR(E2="D2",'Mock CAHPS Adult CQs'!C11,OR(E2="E1",'Mock CAHPS Adult CQs'!C12,ORF(E2="E2",'Mock CAHPS Adult CQs'!C13,OR(E2="E3",'Mock CAHPS Adult CQs'!C14,OR(E2="E4",'Mock CAHPS Adult CQs'!C15,OR(E2="F1",'Mock CAHPS Adult CQs'!C16,OR(E2="F2",'Mock CAHPS Adult CQs'!C17,OR(E,2="F3",'Mock CAHPS Adult CQs'!A18,OR(E2="F4",'Mock CAHPS Adult CQs'!C19,OR(E2="F5",'Mock CAHPS Adult CQs'!C20,OR(E2="F6",'Mock CAHPS Adult CQs'!C21,OR(E2="F7",'Mock CAHPS Adult CQs'!C22,OR(E2="F8",'Mock CAHPS Adult CQs'!C23,OR(E2="G1",'Mock CAHPS Adult CQs'!C24,OR(E2="G2",'Mock CAHPS Adult CQs'!C25,OR(E2="G3",'Mock CAHPS Adult CQs'!C26,OR(E2="G4",'Mock CAHPS Adult CQs'!C27,"")))))))))))))))))))))))))))

I either get an error message that I am missing an opening or closing parenthesis or I get a msg that there is a problem with the formula. It just doesn't tell me what the problem is.

Thank you in advance for any help you can provide.

 

[DanteAmor]

If you explain it in words something like:

If cell E2 = "A1" then I want the value 'Mock CAHPS Adult CQs'!C2
If cell E2 = "A2" then I want the value 'Mock CAHPS Adult CQs'!C3
...

"A1" is a text or do you mean cell A1?

 

[StephenCrump]

It looks like you're using OR() incorrectly.

But assuming this is a typo: 'Mock CAHPS Adult CQs'!A18 and should be C18 (?), you can do this more simply along these lines:

	A	B	C	D	E
1		LookupTable		Lookup	Result
2		A1		A2	Match for A2
3		A2
4		B1
Sheet1

Cell Formulas
Range	Formula
E2	E2	=INDEX(MatchTable,MATCH(D2,LookupTable,))

Named Ranges
Name	Refers To	Cells
LookupTable	=Sheet1!$B$2:$B$4	E2
MatchTable	='Mock CAHPS Adult CQs'!$C$2:$C$4	E2

	A	B	C
1			MatchTable
2			Match for A1
3			Match for A2
4			Match for B1
5			etc
6			etc
Mock CAHPS Adult CQs

 

[TWienholz]

If you explain it in words something like:

If cell E2 = "A1" then I want the value 'Mock CAHPS Adult CQs'!C2
If cell E2 = "A2" then I want the value 'Mock CAHPS Adult CQs'!C3
...

"A1" is a text or do you mean cell A1?

The cell address is E2. A1 is the question code that would be entered into that cell. The actual question text that represents the A1 question code is what is located in the worksheet 'Mock CAHPS Adult CQs'!C2. I'm trying to get that question text to show up on a separate sheet so that it reads like a survey if that makes sense.

 

[TWienholz]

It looks like you're using OR() incorrectly.

But assuming this is a typo: 'Mock CAHPS Adult CQs'!A18 and should be C18 (?), you can do this more simply along these lines:

	A	B	C	D	E
1		LookupTable		Lookup	Result
2		A1		A2	Match for A2
3		A2
4		B1
Sheet1

Cell Formulas
Range	Formula
E2	E2	=INDEX(MatchTable,MATCH(D2,LookupTable,))

Named Ranges
Name	Refers To	Cells
LookupTable	=Sheet1!$B$2:$B$4	E2
MatchTable	='Mock CAHPS Adult CQs'!$C$2:$C$4	E2

	A	B	C
1			MatchTable
2			Match for A1
3			Match for A2
4			Match for B1
5			etc
6			etc
Mock CAHPS Adult CQs

The cell address I'm referencing is E2. A1 is the question code that would be entered into that cell (or A2, B1, B2, C1, D1, etc.). The actual question text that represents the A1 question code is what is located in the worksheet 'Mock CAHPS Adult CQs'!C2. I'm trying to get that question text to show up on a separate sheet so that it reads like a survey if that makes sense. So in essence, if the user enters any one of 26 different question codes in that cell, the actual question text that corresponds to that code will populate on a separate sheet.

 

[DanteAmor]

Try this:

Dante Amor
	E	F
2	B1	Question 4
Hoja14

Cell Formulas
Range	Formula
F2	F2	=INDIRECT("'Mock CAHPS Adult CQs'!C" & MATCH(E2,{"","A1","A2","B1","B2","B3","B4","B5","C1","D1","D2","E1","E2","E3","E4","F1","F2","F3","F4","F5","F6","F7","F8","G1","G2","G3","G4"},0))

 

[StephenCrump]

From your post, I'm guessing you are not familiar with VLOOKUP, or the INDEX/MATCH construct.

At its simplest:

	A	B
1	Code	Questions
2	A1	Question for A1
3	A2	Question for A2
4	B1	Question for B1
5	B2	Question for B2
6	B3	Question for B3
7	B4	Question for B4
8	B5	Question for B5
9	B6	Question for C1
10		etc
11
12	YourCode	YourQuestion
13	B3	Question for B3
Mock CAHPS Adult CQs

Cell Formulas
Range	Formula
B13	B13	=VLOOKUP(A13,A2:B9,2,)

You don't need to individually test for "A1", "A2", "B1" ... etc

 

[TWienholz]

From your post, I'm guessing you are not familiar with VLOOKUP, or the INDEX/MATCH construct.

At its simplest:

	A	B
1	Code	Questions
2	A1	Question for A1
3	A2	Question for A2
4	B1	Question for B1
5	B2	Question for B2
6	B3	Question for B3
7	B4	Question for B4
8	B5	Question for B5
9	B6	Question for C1
10		etc
11
12	YourCode	YourQuestion
13	B3	Question for B3
Mock CAHPS Adult CQs

Cell Formulas
Range	Formula
B13	B13	=VLOOKUP(A13,A2:B9,2,)

You don't need to individually test for "A1", "A2", "B1" ... etc

I am not, but thank you so much because that TOTALLY worked! I knew there had to be an easier way, but as I mentioned I am unfamiliar with the VLOOKUP function. Looks like I have some studying to do! Thank you bunches!

 

[StephenCrump]

Great, I'm glad we could help.

For completeness ... the INDEX/MATCH approach is useful where you don't have a single table with the lookup values in the left hand column:
But if you have the latest Excel version, you can jump straight to XLOOKUP

	A	B	C
1	Code		Questions
2	A1		Question for A1
3	A2		Question for A2
4	B1		Question for B1
5	B2		Question for B2
6	B3		Question for B3
7	B4		Question for B4
8	B5		Question for B5
9	B6		Question for C1
10			etc
11
12	YourCode	YourQuestion
13	B3	Question for B3
14		Question for B3	(if you have XLOOKUP)
Mock CAHPS Adult CQs

Cell Formulas
Range	Formula
B13	B13	=INDEX(C2:C9,MATCH(A13,A2:A9,))
B14	B14	=XLOOKUP(A13,A2:A9,C2:C9)

 

[smozgur]

I am not, but thank you so much because that TOTALLY worked! I knew there had to be an easier way, but as I mentioned I am unfamiliar with the VLOOKUP function. Looks like I have some studying to do! Thank you bunches!

And next time, that would be great if you could mark the post that answered your question instead of your feedback post since it is how we are supposed to do and as it will help future readers.
No more action necessary for this question as I switched the solution post already as you also confirmed. Thanks.