# Multiple IF Statements for a Range of Cells

#### JubberB

##### New Member
Hey! I need to write a formula for multiple IF statements that return a result after searching a range of cells. Here's what I have so far:
Data Range B2:AQ2

If any cell in the range equals any of these values, populate cell AR2 with the appropriate text

="MTG","Meeting"," "

="O","Outbound"," "

="O","Outbound"," "

="OTH","Other"," "

="PJT","Special Project"," "

="TRG","Training"," "

="V","Vacation"," "

Thank you!
JubberB

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### cyrilbrd

##### Well-known Member
Hi, create a name range with the list of what you are looking for.
Mrexcel.xlsx
ABCDEFGHIJ
1abMGTdefglist
2MGT
3O
4OTH
5resultthisPJT
Sheet8
Cell Formulas
RangeFormula
B5B5=IF(SUMPRODUCT(COUNTIF(A1:G1,list))>=1,"this","that")

#### SteveOranjinSteve

##### Board Regular
This won't do you any good if there are MORE than one of those values in the range. Additionally, this will only return the FIRST value that appears in the range. Irrespective of if the other ones have greater value, or whatever.

VBA Code:
``=IFS(B2:AQ2="MTG","Meeting",B2:AQ2="O","Outbound",B2:AQ2="OTH","Other",B2:AQ2="PJT","Special Project",B2:AQ2="TRG","Training",B2:AQ2="V","Vacation")``

#### JubberB

##### New Member
=IFS(B2:AQ2="MTG","Meeting",B2:AQ2="O","Outbound",B2:AQ2="OTH","Other",B2:AQ2="PJT","Special Project",B2:AQ2="TRG","Training",B2:AQ2="V","Vacation")
So I pasted this into the cell and got ?NAME
What is the cell contains another value that what we listed? Can we leave the cell blank?
Also, is pasting it like a formula the way to do it, or do I have to paste it in a CODE?
Sorry.

#### SteveOranjinSteve

##### Board Regular
So I pasted this into the cell and got ?NAME
What is the cell contains another value that what we listed? Can we leave the cell blank?
Also, is pasting it like a formula the way to do it, or do I have to paste it in a CODE?
Sorry.

So B2:AQ2 was correct right? Try typing it in? I tried it before I sent it out. It works.

#### SteveOranjinSteve

##### Board Regular
Try this:

I've added an iferror statement, so if nothing is returned, it still returns a value. You should change the "if error" value clause to represent something that adequately represents what happens when there are no values in any of those cells for your data.

=IFERROR(IFS(B2:AQ2="MTG","Meeting",B2:AQ2="O","Outbound",B2:AQ2="OTH","Other",B2:AQ2="PJT","Special Project",B2:AQ2="TRG","Training",B2:AQ2="V","Vacation"),"No Value")

#### SteveOranjinSteve

##### Board Regular
What version of excel do you have? That kind of error mostly occurs when there is a misalignment between formulas. Like if you were to type in, "Vlokup" for "Vlookup". Do you have a version of excel earlier than 2016?

2016

#### Norie

##### Well-known Member
What's to happen when more than one of the values appears in B2:AQ2?

What would go in AR2 then?

#### JubberB

##### New Member
I really appreciate the help. Not going to lie; never entered code before.

1,089,515
Messages
5,408,732
Members
403,224
Latest member
rholmesa

### This Week's Hot Topics

SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
• two formulas needed
Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
• Dynamic Counts
Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
• Help Excel formula eliminate duplicate values and keep only 2 identical rows.
as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
• Macro Compile Error Sub or Function not defined
Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
• Last row combined with Current Region VBA
I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...