Simplify Data Organization with the IFS Function in Google Sheets
Learn how to simplify data organization in Google Sheets using the IFS function to manage class choices effectively. This video demonstrates how to implement the IFS function for multiple conditions and handle errors gracefully.
We have an interesting problem here. We have a list of people who have signed up for their choice of classes. They have a first choice, second choice, third choice. And then they also have a class which they say no, I don't want to attend. And so we want to turn this into data . We want to be able to know, based on the class, ensemble acting, which students have selected as their first choice, second choice, third choice, or no. And we need to fill this in. The person asking this question has filled it in a possible idea of using the if function and saying does C2 equal ensemble acting? If so, 1. And what this is going to do is it's going to be very specific to one expression , right? We're just looking for ensemble acting. But really what we want to know is which choice, if any, is this? Now there is another solution here. Bart has a solution here which is using the left ifna and the filter function . Checkbox or Dropdown?
I'm going to show you an actually much simpler idea here and we're going to use something similar to the if formula . I'm going to first duplicate and share with you this better sheets solution. What we want to use is the ifs formula . Ifs provides us a con multiple conditions and the formula will look for the first condition that is true and then give you that value. So the first condition we're going to look for is C2 equal To G1. And if that's true, put a 1. Now we're not putting 1 within quotes because we want the num, actual number 1. If you want some other actual text, 1, we do have to put that in quotes. But for the number 1, and we want to keep it as a number, we can leave it as 1. Now, we just need to put in the conditions as we know them to be true, 5 Ways to use IF() Formula to Create Magic in Your Sheets 5 Magical Ways To Use the IF() Formula in Google Sheets IF All Above Are Filled Conditional Formatting
and we want to check at, in that order. So we just put another comma. And we select D2 is equal to ensemble acting. And now we can see that this is going to be the same, the same sort of motion, but we need to add a 2 here. And then if it's in the third column, we need a 3. Again, we will do F2 equals ensemble acting. And if so, put no. So that gets us our answer three. But we're missing a few things in this little bit of nuance we want to add to this. Just so that it can actually copy and paste all the way down and all the way across. So let's start copy and pasting and we see these are wrong. Why are they wrong? It's because, I'm not sure if you can see this. But as we copy and paste it, the cells will change. We need to have a method in which to hold certain aspects of these ranges . How Can I Compare Two Tabs for Differences? Figure Out Frequency of Numbers Highlight Duplicates of Two Columns
For instance, the C column must be the C column all the time, but when we copy, paste down, we want this C column, C2, to become C3, which if we do not put a dollar sign in front of it, it will change. But if we do put a dollar sign in front of it, it will not change. And this G1, as we go down, we want the 1, Change, but as we go across, we don't want the 1 to change. And as we go across to the right, we do want the G to change. So we put a dollar sign in front of the 1 to hold that as we copy and paste it. And we do that all along the entire formula . So we hold the D column and hold the 1 in G1. Hold the E column and hold the 1 in G1. Hold the F column and hold the 1 in G1. And so now we have these dollar signs which are going to be holding basically that reference , either the column or the row. How to Refer to Other Cells - A1 and R1C1 Explained GPT in SHEETS Count First Digit of Cell
And as we copy paste, we now see, yeah, ensemble acting does not appear here, does not appear here, and it is 1 here. So we're getting the correct answer. And as we copy paste all the way across, we see that yes, the G column is changing to look at the new header here. So in H1, it'll be H's here, it'll be I's here. Perfect. And now, we can copy and paste this all the way down and copy this whole row , paste it all the way down. So there's one more nuance we need to figure out, and we have to fix, is these NAs, when there's no match , meaning ensemble acting does not appear anywhere here. Now, this will be completely based on what you want to do here. Maybe you want it to say nothing at all, or you want to say no choice. Anders Asks: Can I Highlight Whole Row if Certain Columns have text? VLOOKUP NOT WORKING, Getting #N A
In either case, we're going to put at the beginning of this, if NA. And at the end we're going to add a comma, so if this ifs function doesn't result in any value, meaning you get an N A error , what should we do? And I'm going to say no match for now, so you can see that it says no match . And we can double check this. Does ensemble acting appear? No. So this is the perfect, that is the perfect answer, no match . But, let's say you wanted it to just be blank because we are looking at this data , we do not care if somebody does not have their choice yet. So instead of no match , I'm actually going to delete all the quotes and the text and leave nothing there. And now there is no text shown. This is very good if you're doing data analysis and you just need to see the numbers that exist. That gives you an option of putting some text here or nothing at all whatever Creating Blank Cells For Good Reasons with IfError Formula and if(isBlank) Formula Remove Non Numbers
you need to do for your business logic, that's what you put in that ifna. But hopefully, this ifs function and this ifs formula makes a lot more sense. Then the if function . This ifs will automatically go through each of these and find the first one that matches it and then give us that value. Hopefully that helps you understand this more and gives you the correct answer. 5 Ways to use IF() Formula to Create Magic in Your Sheets