IF/OR function

Neisha Fredericks

Hi All,

 

I am trying to create a formula (assuming a combo of if/or is the way to go), but keep getting "incorrect argument set. They work independently, but not when I combine them. I basically want to search for either of the following in a cell and then return a value.

Look for any of the following in one column:

• Content/Social Media - e.g. blog, press releases, and video broadcasts
• Technical Writing/Documentation
• Graphic Design - e.g. UX, swag, and logos
• Video Production
• Website - e.g. design and content

And if any of those options are found then return "Content creation" into a different column same row.

These is the formula I am using: =IF(OR(Topic@row = "Technical Writing/Documentation", Topic@row = "Content/Social Media - e.g. blog, press releases, and video broadcasts", "Content creation"))

If I enter them as separate if statements it works fine and is blank if it's not found.

I was using this as my starting point, but I really want to have it query for multiple things. The above is the example for Content creation. I also want the formula to look for the following and return "Community Essentials + Infrastructure"

• Project Health Metrics
• Infrastructure Support
• Managed Hosting
• SysAdmin

Am I trying to do to much? Any ideas on how to accomplish this?

Brian W

It looks like you just have a misplaced end parenthesis:

=IF(OR(Topic@row = "Technical Writing/Documentation", Topic@row = "Content/Social Media - e.g. blog, press releases, and video broadcasts"), "Content creation")

Here is your complete equation:

=IF(OR(Topic@row = "Technical Writing/Documentation", Topic@row = "Content/Social Media - e.g. blog, press releases, and video broadcasts", Topic@row = "Graphic Design - e.g. UX, swag, and logos", Topic@row = "Video Production", Topic@row = "Website - e.g. design and content"), "Content creation", IF(OR(Topic@row = "Project Health Metrics", Topic@row = "Infrastructure Support", Topic@row = "Managed Hosting", Topic@row = "SysAdmin"), "Community Essentials + Infrastructure"))

Paul Newcome

If you expect anything in these requirements to change, I would actually recommend setting up a reference table and using an INDEX/MATCH function to pull the correct data. That will provide you with a lot more flexibility and ease of use if anything needs to be changed, expanded, or deleted. If you are interested in this particular solution, let me know, and I will lay it all out for you. It is actually pretty straight forward, and I can walk you through it pretty easily.

Neisha Fredericks

hi Paul,

 

It is highly likely it would change. I would really appreciate you walking me through this. 

 

Thanks so much for offering!

 

Neisha

Neisha Fredericks

Thank you so much for your assistance Brian. I haven't had a chance to see if it works yet as I've been in a conference this week but just wanted you to know I appreciate the feedback.

Paul Newcome

No worries! Basically what we will do is build a basic table and then use an INDEX/MATCH (more flexible version of a VLOOKUP) to pull the correct data. For this example I will assume that your table will be on the same sheet. 

 

First we will add 2 columns. These can be hidden after everything is set up to help keep your sheet looking clean. We will just call them "Table 1" and "Table 2" for this walk through, but you can name them whatever you want. I won't be typing out the entire table, but you can use the below as a reference (column headers in bold).

 

Table 1                              Table 2

Videos Production             Content Creation

Managed Hosting              Comm Essentials + Infrastructure

Graphic Design                 Content Creation

Website                             Content Creation

SysAdmin                          Comm Essentials + Infrastructure

.

The two functions we will be using:

=INDEX([range to pull data from], [row number], [column number optional])

Pulls data from a range specified in the first part based off of a row number and an optional column number.

=MATCH([search value], [range to search], [match type])

Provides a number where a specific value can be found within a range. It looks at your specified range like a grid and will read it the same way the English language is read: Left to right/top to bottom. A few samples of ranges and there corresponding MATCH results here...

Across a row: 1     2    3    4     5

Down a column: 1

(This is how          2

  we will be           3

  using it)               4

                               5

In a grid: 1    2    3

                 4    5    6

                 7    8    9

.

So the basics of the formula are that we will be using the INDEX function to pull data from the table and we will use a MATCH function to provide a row number for the INDEX function. Since we will be using single columns for our ranges, we do not need to specify a column number for the INDEX function. Let's get to building...

 

What is our main function?

=INDEX(

.

What is our range to pull data FROM? We want to actually want to display the value from the second column of our table.

=INDEX([Table 2]:[Table 2],

.

What row do we want to pull it from? Well we don't know exactly which row number, and it can change based on whatever is entered into the Topic column. This is where we use the MATCH function to pull a row number from a vertical range (column).

=INDEX([Table 2]:[Table 2], MATCH(Topic@row,

.

Now that we know what to look for in our MATCH function, where are we looking for it? The first column of the table.

=INDEX([Table 2]:[Table 2], MATCH(Topic@row, [Table 1]:[Table 1],

.

And we want an EXACT match, so...

=INDEX([Table 2]:[Table 2], MATCH(Topic@row, [Table 1]:[Table 1], 0

.

That closes out our MATCH function which also completes the Row # portion of the INDEX function. Since we are only looking at one column in for the INDEX range, we don't need to specify a column number, so we can go ahead and close out that function as well.

=INDEX([Table 2]:[Table 2], MATCH(Topic@row, [Table 1]:[Table 1], 0))

.

And there you have it. You have now officially built an INDEX/MATCH which (in my opinion and that of many others) is so much better than a VLOOKUP.

 

Here's why: Since we are looking at entire columns for our ranges, you can add as many different values to your table as you want (assuming you keep it under 5,001) on both sides of the table. You can update any text and delete any values. You can even reorder the columns since we are looking at the columns separately. And sorting by rows won't affect anything since the data/value will stay on the same row anyway.

 

This will all keep you from having to dig into long IF(OR( formulas just to tweak a letter here or update a value there or add/delete something from your list. Just update your table, and you're done!

Neisha Fredericks

Paul, 

Finally had a chance to get back to this today. Wanted you to know it worked beautifully!!!!!! The only change I made was to create the index in another sheet, but cool breeze with being able to reference another sheet. 

Thank you so much for such amazing instructions. Gold star!!!!!!

 

Neisha

Paul Newcome

Happy to help!