A classic problem. Your VLOOKUP is hard-coded to return the 3rd column from a table. But then you insert new columns 2, 3, and 4 in the table. Instead of automatically shifting to return the correct 6th column, VLOOKUP still blindly pulls from the 3rd column. Mike and Bill show two alternate ways to solve this problem.
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast. I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from Excel Is Fun. This is our episode 172 - Hard-coded VLOOKUP (third argument)!
Alright, so today's question, Luis from YouTube has a VLOOKUP table, and it is working perfectly.
Right, we choose the item from the list, and these two VLOOKUPs get the product and the price. But occasionally though, he says he has to insert a new column, like over here, he inserted the cost column, and because this VLOOKUP is hard-coded to use column 3 and column 4, we're now getting column 1 2 3, the cost is showing up as the product, and the supplier is showing up as the price. How can we avoid that whole situation? So here's what I'm going to do, instead of hard-coding the number 2 in there, or 3 in this case to get the product, I'm going to say that I want to do a MATCH of this heading, right above me, comma, this list of headings up here, I'll press F4 to lock that down, and then ,0 , close that. So now instead of saying "Hey, it's in column 3", it will always go search and see where that particular item is, and actually as I look through this here, I'm going to add a & to the A, so what we're always reaching back to column A, and we'll copy that over. Alright, so exact same results now, but then later on, if we come along and insert some new stuff, I E C Enter, and we have our cost, you see this down here, it continues to get the right items, essentially getting item 4 and column 5 because it's using the MATCH. Let's take a quick look at Formulas, Evaluate Formula, Evaluate, Evaluate. Right here, that match when we calculate that, see it's returning the number 4, because it's the fourth position. Alright Mike, let's see what you have!
Mike: Thanks MrExcel! VLOOKUP and MATCH, THAT is absolutely my first reaction to this problem.
Also I like MATCH, but let's do it differently.
I'm going to click over on this sheet right here. So I'm going to delete those, and instead of VLOOKUP and MATCH, I'm going to use a database function, =DGET ! Now, the D, DSUM, DMAX, DMIN, D standard deviation(?), those are database functions, and DGET goes and gets something.
Hey, the argument you need is "database", and you have to have field names in the first row, and then records and subsequent rows.
I'm going to lock that with F4, comma, you got to tell this database function which field you want to make a calculation upon. In our case, we're going to get something, and it's got to be exactly like the field names up here. Whoa, I already have that right there, so relative cell reference, comma. Now the criteria, this is often a stickler for when you can actually use DGET. Now notice I'm going to highlight these two cells and lock it, but it won't work, because criteria requires a field name, and the criteria below. ) , CTRL+Enter!
No way is it going to work, but guess what, I kind of like "ID", the field name, better than "Lookup". So if you can do that, BOOM, I do that, look at that, DGET went and did it! And when I copy it to the side, absolutely it gets the price. Now, let's go ahead, MrExcel inserted and noticed the form- the cell references in the formula update. Here I'm just going to point to the edge and move it, and when I go down here, notice that expands it too, it's the same as inserting, right? And so did that work, you bet that it worked, totally!
Because it's looking up product, the DGET function is programmed to look through field names in a proper database! Now I'm going to open up my clip board here, I'll click in this top cell right here, I copied the data from that first sheet, I'll close the clipboard, and BOOM! Now it updates! Alright, throw it back to MrExcel!
Bill: Hey alright, the database function.
DGET, that is a beautiful function, I was using that a lot back before Pivot tables came around 1990s, and it uses the field name.
What a brilliant trick, that is an awesome, awesome way to go. Point to Mike for remembering DGET.
Alright well, thank you everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and excelisfun!
Alright, so today's question, Luis from YouTube has a VLOOKUP table, and it is working perfectly.
Right, we choose the item from the list, and these two VLOOKUPs get the product and the price. But occasionally though, he says he has to insert a new column, like over here, he inserted the cost column, and because this VLOOKUP is hard-coded to use column 3 and column 4, we're now getting column 1 2 3, the cost is showing up as the product, and the supplier is showing up as the price. How can we avoid that whole situation? So here's what I'm going to do, instead of hard-coding the number 2 in there, or 3 in this case to get the product, I'm going to say that I want to do a MATCH of this heading, right above me, comma, this list of headings up here, I'll press F4 to lock that down, and then ,0 , close that. So now instead of saying "Hey, it's in column 3", it will always go search and see where that particular item is, and actually as I look through this here, I'm going to add a & to the A, so what we're always reaching back to column A, and we'll copy that over. Alright, so exact same results now, but then later on, if we come along and insert some new stuff, I E C Enter, and we have our cost, you see this down here, it continues to get the right items, essentially getting item 4 and column 5 because it's using the MATCH. Let's take a quick look at Formulas, Evaluate Formula, Evaluate, Evaluate. Right here, that match when we calculate that, see it's returning the number 4, because it's the fourth position. Alright Mike, let's see what you have!
Mike: Thanks MrExcel! VLOOKUP and MATCH, THAT is absolutely my first reaction to this problem.
Also I like MATCH, but let's do it differently.
I'm going to click over on this sheet right here. So I'm going to delete those, and instead of VLOOKUP and MATCH, I'm going to use a database function, =DGET ! Now, the D, DSUM, DMAX, DMIN, D standard deviation(?), those are database functions, and DGET goes and gets something.
Hey, the argument you need is "database", and you have to have field names in the first row, and then records and subsequent rows.
I'm going to lock that with F4, comma, you got to tell this database function which field you want to make a calculation upon. In our case, we're going to get something, and it's got to be exactly like the field names up here. Whoa, I already have that right there, so relative cell reference, comma. Now the criteria, this is often a stickler for when you can actually use DGET. Now notice I'm going to highlight these two cells and lock it, but it won't work, because criteria requires a field name, and the criteria below. ) , CTRL+Enter!
No way is it going to work, but guess what, I kind of like "ID", the field name, better than "Lookup". So if you can do that, BOOM, I do that, look at that, DGET went and did it! And when I copy it to the side, absolutely it gets the price. Now, let's go ahead, MrExcel inserted and noticed the form- the cell references in the formula update. Here I'm just going to point to the edge and move it, and when I go down here, notice that expands it too, it's the same as inserting, right? And so did that work, you bet that it worked, totally!
Because it's looking up product, the DGET function is programmed to look through field names in a proper database! Now I'm going to open up my clip board here, I'll click in this top cell right here, I copied the data from that first sheet, I'll close the clipboard, and BOOM! Now it updates! Alright, throw it back to MrExcel!
Bill: Hey alright, the database function.
DGET, that is a beautiful function, I was using that a lot back before Pivot tables came around 1990s, and it uses the field name.
What a brilliant trick, that is an awesome, awesome way to go. Point to Mike for remembering DGET.
Alright well, thank you everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and excelisfun!
