Multiple IF Statements for a Range of Cells

JubberB

New Member
Joined
Nov 1, 2019
Messages
28
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
 

Some videos you may like

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
Joined
Feb 2, 2012
Messages
4,071
Office Version
365
Platform
Windows, Mobile
Hi, create a name range with the list of what you are looking for.
Then adapt on this:
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
Joined
Nov 18, 2019
Messages
78
Office Version
365, 2019
Platform
Windows
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
Joined
Nov 1, 2019
Messages
28
=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
Joined
Nov 18, 2019
Messages
78
Office Version
365, 2019
Platform
Windows
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
Joined
Nov 18, 2019
Messages
78
Office Version
365, 2019
Platform
Windows
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
Joined
Nov 18, 2019
Messages
78
Office Version
365, 2019
Platform
Windows
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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
What's to happen when more than one of the values appears in B2:AQ2?

What would go in AR2 then?
 

Forum statistics

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

This Week's Hot Topics

  • help please
    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...
Top