Multiple IF Statements for a Range of Cells

JubberB

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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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")
 
Upvote 0
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")
 
Upvote 0
=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.
 
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0
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?
 
Upvote 0
What's to happen when more than one of the values appears in B2:AQ2?

What would go in AR2 then?
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,724
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top