Find a Value in a Range

Beginner

About this Tutorial

I'll show you how to easily find a specific value in a range using two powerful formulas: VLOOKUP and INDEX MATCH.

With VLOOKUP, we can search for a value in a column and retrieve information from the same row.

I'll explain how to set it up and why the "false" parameter is important. Then, I'll introduce INDEX MATCH, which allows us to search for a value in one column and retrieve information from another column.

I'll demonstrate how to use it to find a name based on a number and vice versa. This technique is incredibly useful when you need to look up information based on a specific value.

Plus, I'll share a neat trick to make the search even more dynamic. So, let's dive in and master these versatile formulas together! 🚀

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

INDEX() MATCH() VLOOKUP()

Video Transcript

<div>0:00 Hey, so you simply want to find a value in a range . Let's say we have an A column here with a bunch of numbers all the way down. 0:06 Thousand different numbers. We have in the C column a bunch of different names, and we're like, ah, we just want to know if like there's one number we think of is it in this list? 0:16 And let's like pick five, four, six, six. So if we want to take this number and we're like, do we really want to scroll through this or do we want to copy it and command F, find it in the sheet, go down to it. 0:31 This is a bit complicated, right?

We want to do this in a formula . So I'm going to show you two formulas that you can do this with one, which is going to be VLOOKUP . 0:41 Search key is going to be the thing you're looking for. And then the range is going to be here and index , I will show you why this is important later, but right now we're going to do one and then I'm going to do comma. 0:51 And we're going to do false. I do false all the time is sorted . Even if it is sorted and I know it's sorted, this is just sort of a fail safe for me. 1:00 I always put false. It's also optional. And there it is.

It's, it's right there, but like, where is it in here? 1:07 And what does it matter? Well, we can use match to say, okay, let's search for that key as well. That five, four, six, six, one, five. 1:15 Let's do the exact same range . We're going to select a colon a here, the a column. Our search type is going to be zero and it says one five four. 1:25 What does that mean? Let's scroll down and see if it's at online one five four and it is. And here is the name Marcus Pennington. 1:34 So why I chose the lookup here is because often we're looking for a value.

And we want other information from that. 1:44 So instead of doing VLOOKUP a colon a, I'm actually going to do a colon C. I'm going to include the names here. 1:54 And now if I just hit enter, nothing changes. But if I change this index from one to three, which means the third column from the left side, counting from left to right, I hit enter. 2:07 And now I get Marcus Pennington. So that's pretty cool. If you're looking for information based on the value in that range . 2:14 And if we want to use basically VLOOKUP presupposes that on.

The left side is where you're going to have your key information, your primary key, which is unique numbers or unique names. 2:29 It's always going to be on the left, but let's say we're looking for let's say we want to do this in reverse. 2:34 We're like, actually we want to find Marcus Pennington and we want to get the number that's in the A column. 2:40 And for some reason we can. Not move the A column.

We can't move it over to the right of the C column so that the C column of the names is on the left side. 2:48 VLOOKUP presupposes that the left side is the thing you're searching for and then you're going to get information to the right. 2:54 So we can use this match over here and we're just going to wrap it in an index . So match over here gets us the row. 3:04 We know which row it is. Now we just need to get the row in the C column. So we just say equals index . 3:12 Reference is going to be we have could be A or yeah A. Our row is going.

To be this F2 here and our column is going to be one. 3:26 And there it is. There's the number . We can also change A to A to C to C to get the C column and we get the name. 3:36 So this match can be wrapped with index . So let me show you that all at once. Let's say we have our name. 3:43 We're looking for Marcus Pennington here. We have in the D1 here. We have Marcus Pennington equals index and we're going to reference where are we going to end up? 3:53 Well, we want to end up in a column.

We're going to do a comma and now we're going to write match in for row. 4:00 This search key is going to be Marcus Pennington. The range is going to be the C column. That's where we're going to look for Marcus. 4:08 We're going to find the row number and then we're going to get the row number in the A column. Now insert type zero and we can put a column here for one if we want and there we go. 4:18 We have now a little bit of search thing here. Where we can search for any name and get the number over here on the left side. 4:26 So this is really really helpful.

And if you're looking for something a little less dynamic, meaning this D2 is referencing Marcus Pennington. 4:35 If you're like, I'm always going to look for this text no matter what and I don't want it in another cell. 4:41 I just want the. Result instead of D2 put in quotes in double quotes the name Marcus Pennington. And there you go. 4:49 The exact same result here where we're looking up Marcus Pennington. We're looking up in the C column. Tell me which row is it in. 4:57 We're saying give me exactly the row. And then that row number is going to be the.

Row number of the index of the A column. 5:05 So index match is a really good. Used to find a value looking up a value in this a column from a C column because it's the reverse of V look up and not the reverse. 5:16 It's a much more flexible version of V look up. V look up absolutely can do this, but you must have the thing you're looking through on the first column and then to the right, you need to have all the values that you want to get out of. 5:31 So V look up is a little less flexible.

And there's also X look up and H look up, but I really like index match . 5:39 I find it extremely flexible, versatile. To find that value and arrange and get all the other information you want from it across a whole sheet. 5:49 You can also re retrieve much more information here if you want. Bye.</div>