Enter Name Get Grades
Learn how to create a dropdown menu in Google Sheets that allows you to select a name and retrieve corresponding grades using the FILTER function. This video also covers handling multiple entries and using the INDEX function to manage duplicates effectively.
We have some grades. We have John, Jane, Mike, Sarah and Alex, and all of their grades, but we want to be able to enter a name and get their grades. So in a two, we want a dropdown menu that says all of their names. We can say dropdown menu. We can even say dropdown menu from a range . Select that range and get all of those names, even if there's duplicates or anything, like you can just put a range and it'll grab all of those names. You don't even have to type them in. Let's click done. So now what's gonna happen in our A two cell is that when we select one of these names, the text in the cell is that name. So it is just like deleting it and typing Jane and or Mike, whatever it is. We type, the text of that cell will be this, this dropdown menu actually is
not very, um, complicated or magical other than it's just selecting the text and it gives us the user or someone else, the user, the ability to just click and, and select the text. But at the heart of the cell, the actual data in the cell is just text in B two and F two here. Where we want to go to this list and find all of the grades here, we can do it one of two ways. I prefer this way, filter , using filter and saying the range . So I'm gonna select the range of what I'm looking at and I'm gonna say. Actually, I don't need the name 'cause I have the name here. So I actually need only B six to F 10.
I'm looking at that data . But what, based on what? Well, the condition . The condition that the A six through 10 is equal to the text in A two N hit enter. And is that correct, Jane? 91 89? Yes it is. Correct. So this is taking. The range that we're all looking at saying based on which one in the A column is equal to the A two, we're gonna go see that. And that's, I think, the most elegant, the issue becomes when there's multiple. So I don't wanna go too deep into it, but let's say Jane's also here and this score is here, say this score. So now we have multiple genes.
We might not use these numbers. B six to F 10, we might use B six to F and a six to A, and now we have multiple things. So I prefer to see this to say, oh, Jane has multiple grades. Oh, that's interesting. That's what I prefer. I prefer to see this. And if there is some data here, let's say this is actually up here, we're going to get a reference error and that reference errors is gonna say array result has not expanded because it would overwrite data in B three. So what I would suggest is making a little more space and seeing if that solves it, or if you already know there are multiples decide. Do you want to only get the first one? And that we're gonna wrap index , and we only want one row.
The index is saying just give us the first one, and this is comma row, comma one, which is the row. But if we only want the first row and the first column, we can do. Comma one comma one or comma one, comma two, to get the second column and see that 89 is not math. Actually her Jane's first science over here in C column. But if we do one, we can say it's 91. That is what Jane caught in math. So that index quote unquote, does fix multiple results of that filter . However, it's going to just point it. One thing, which should make sure that that is the right thing you wanna point at it is gonna be the top one in your data range . Or you can say, Hey, give me the second one. Always give me the second one.
But if it's, let's say John, let's doesn't have, looking at the second row, it's 92. This is not correct for John. It is correct for Jane, but not for John. So you have to be very careful using index . That's what you want. What is John doing here? So I prefer just seeing the whole filter and getting an error if there's multiples, and then figuring out there, what do I want? Do I actually wanna come in and just delete something? Maybe second grades. If there's duplicates of some kind, maybe it is a direct duplicate and you're still gonna get a reference error . But if you're like, well, even on duplicates, I just want one of them. We can wrap this with Index comma one and get it. Even if there's duplicates, we're gonna get a result, not a reference error.
You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.