Please help fix formula syntax!

TWienholz

Board Regular
Joined
Aug 3, 2016
Messages
54
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. :)
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,572
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
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:

ABCDE
1LookupTableLookupResult
2A1A2Match for A2
3A2
4B1
Sheet1
Cell Formulas
RangeFormula
E2E2=INDEX(MatchTable,MATCH(D2,LookupTable,))
Named Ranges
NameRefers ToCells
LookupTable=Sheet1!$B$2:$B$4E2
MatchTable='Mock CAHPS Adult CQs'!$C$2:$C$4E2


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

TWienholz

Board Regular
Joined
Aug 3, 2016
Messages
54
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

Board Regular
Joined
Aug 3, 2016
Messages
54

ADVERTISEMENT

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:

ABCDE
1LookupTableLookupResult
2A1A2Match for A2
3A2
4B1
Sheet1
Cell Formulas
RangeFormula
E2E2=INDEX(MatchTable,MATCH(D2,LookupTable,))
Named Ranges
NameRefers ToCells
LookupTable=Sheet1!$B$2:$B$4E2
MatchTable='Mock CAHPS Adult CQs'!$C$2:$C$4E2


ABC
1MatchTable
2Match for A1
3Match for A2
4Match for B1
5etc
6etc
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

Well-known Member
Joined
Dec 3, 2018
Messages
13,572
Office Version
  1. 2007
Platform
  1. Windows
Try this:

Dante Amor
EF
2B1Question 4
Hoja14
Cell Formulas
RangeFormula
F2F2=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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

At its simplest:

AB
1CodeQuestions
2A1Question for A1
3A2Question for A2
4B1Question for B1
5B2Question for B2
6B3Question for B3
7B4Question for B4
8B5Question for B5
9B6Question for C1
10etc
11
12YourCodeYourQuestion
13B3Question for B3
Mock CAHPS Adult CQs
Cell Formulas
RangeFormula
B13B13=VLOOKUP(A13,A2:B9,2,)

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

TWienholz

Board Regular
Joined
Aug 3, 2016
Messages
54
From your post, I'm guessing you are not familiar with VLOOKUP, or the INDEX/MATCH construct.

At its simplest:

AB
1CodeQuestions
2A1Question for A1
3A2Question for A2
4B1Question for B1
5B2Question for B2
6B3Question for B3
7B4Question for B4
8B5Question for B5
9B6Question for C1
10etc
11
12YourCodeYourQuestion
13B3Question for B3
Mock CAHPS Adult CQs
Cell Formulas
RangeFormula
B13B13=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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
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

ABC
1CodeQuestions
2A1Question for A1
3A2Question for A2
4B1Question for B1
5B2Question for B2
6B3Question for B3
7B4Question for B4
8B5Question for B5
9B6Question for C1
10etc
11
12YourCodeYourQuestion
13B3Question for B3
14Question for B3(if you have XLOOKUP)
Mock CAHPS Adult CQs
Cell Formulas
RangeFormula
B13B13=INDEX(C2:C9,MATCH(A13,A2:A9,))
B14B14=XLOOKUP(A13,A2:A9,C2:C9)
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,538
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.
 

Forum statistics

Threads
1,141,062
Messages
5,704,062
Members
421,326
Latest member
pfaustino

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top