Find Column with Header to Bullet Proof your Vlookup

Intermediate

About this Tutorial

Make your VLOOKUP formulas flexible and your data move-able with this trick.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

HLOOKUP() MATCH() VLOOKUP()

Video Transcript

<div>0:00 So Dave, a BetterSheets member, had this question. When using VLOOKUP and I move columns around, how can I still find the data , but by the header ? 0:15 So, we have here a, an example. We have project, salesperson, sales, and value, and we're trying to create a snapshot here. 0:24 And if you're trying to create a sales snapshot, and we just want to search through the projects and get all the data you want data from that row, like here, Seattle, we want to see the salesperson. 0:35 We're going to do something like this. We're going to do VLOOKUP .

We're going to use the search key G2, which is this ah selection here, the dropdown . 0:45 We're going to use the range , is going to be A colon D, all of the range that we're looking at. 0:51 And then with VLOOKUP , the project needs to be in the first column, and now we are going to look in which column do we want the data from, where you want it. 1:00 Two, second one, not B, but the number two. Is it sorted? I like to always put false here, just in case. 1:08 We can then copy paste this and actually move, make sure that G2 stays the same.

But instead of the index being two, we're going to use three for sales and four for value here. 1:23 So again, we have our snapshot here. Now, if we change this to Boston, we see that all of the information changed. 1:29 If we change it to let's say, Louisiana, all the information changed, and it's just grabbing all of that row data , right? 1:35 But here's the issue, right? We're going to take the C column and let's say we, we're like hey, actually we want the sales before the salesperson. 1:44 We don't really, the most important is to see this project and the sales.

And look over here on the right, our data has now been misaligned in our snapshot. 1:54 We have Louisiana, salesperson is 46. It's looking in the second column, but the second column is sales now. How do we fix it so that we can move this around and still get the same data ? 2:05 You might have tried something like HLOOKUP to try to do that, but in fact the answer is we're going to replace our number two with MATCH , M-A-T-C-H, and we're going to use the search key of what are we looking at, what header are we looking at? 2:21 And in this case it's in the F column here.

We have written exactly the same header , the same text as the header . 2:29 So we're going to use that as our matching. We can obviously use text here, we can write in salesperson if we want, but I'll show you that the F3 is going to be easier. 2:42 The range is going to be this column, not column, the row one, the headers that we're looking for. Search type, again, this I like to just, even though it's optional, I like to put zero. 2:55 and now we have the correct person here. We're looking up salesperson and I will show you that.

it will not change even if we put F3 here. 3:06 Hit enter. So now we're looking at this text, looking for salesperson here, finding in the second column. Let's see if it changes if we move it, and no, it did not change. 3:15 You saw the sales changed. So let's copy that and paste it here, and instead of F3 we're going to do F4. 3:24 we can do that. There we go. And instead of F4, we're going to look at F5. There we go. So now we can move things around and we're still getting the same data in our snapshot. 3:39 So we've actually found the column with the header .

So match is going to allow us to enter in some text and get back a number , which is really cool and is the solution to finding the column with the header here. 3:54 And now we can create a snapshot that is bulletproof and movable and flexible for all of our purposes.</div>