From a popular certification test - how to do a VLOOKUP when column A is in outline format
Advice from Bill: Anything on that test is not that difficult!
Bill's Method: Do the straight VLOOKUP
Wrap the VLOOKUP in the IFNA function. If you get an #N/A, then use the value from above
This method only works in Excel 2013 or newer. In Excel 2010, use IFERROR instead.
Mike's Method:
Inside the VLOOKUP function, use the LOOKUP function to find the last text in an expanding range.
LOOKUP ZZZZZZ so it will return the last non-blank item
Use an expanding range to find the last text value in the column down to this point
In Mike's second method, he shows where trailing spaces will cause Bill's formula to fail.
Mike's second method uses ISBLANK and VLOOKUP to detect if a country code is missing.
Advice from Bill: Anything on that test is not that difficult!
Bill's Method: Do the straight VLOOKUP
Wrap the VLOOKUP in the IFNA function. If you get an #N/A, then use the value from above
This method only works in Excel 2013 or newer. In Excel 2010, use IFERROR instead.
Mike's Method:
Inside the VLOOKUP function, use the LOOKUP function to find the last text in an expanding range.
LOOKUP ZZZZZZ so it will return the last non-blank item
Use an expanding range to find the last text value in the column down to this point
In Mike's second method, he shows where trailing spaces will cause Bill's formula to fail.
Mike's second method uses ISBLANK and VLOOKUP to detect if a country code is missing.
Transcript of the video:
MrExcel: 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 ExcelIsFun.
This is our episode 189: VLOOKUP from Outline Data.
Hey, I'm redacting parts of this question, but it's a great question: "Unfortunately, my friends took the-- "I can't tell you what"-- exam again and still had a problem with this question.
We have created a spreadsheet to mirror the question.
We are totally clueless on this formula.
Also, answer must be name of country, not true or false.
Most sincerely,--” and then, I redacted their name, but there's five of them who are trying to figure this out.
Alright, so here's the question: How to create a VLOOKUP when Column A has blanks.
I want to populate Column B with a country, and here's our Lookup Table, from Code to Country, like that.
For example, A2 will be Afghanistan, and that will repeat all the way down to 10.
And then we'll get to the new one.
AX will be the Aland Islands, A11 to A18.
The example of the expected results is here.
Alright.
Well, hey, I feel your pain, but you are over-thinking this.
Anything that's on that-- can't tell you the name of the exam-- is not going to be as hard as what you are making this out to be.
It has to be super simple: =VLOOKUP(A2, Go do a VLOOKUP in this Lookup Table, here...F4, 2, false...
=VLOOKUP(A2,'Lookup Table'!$A$2:$B$26,2,False).
Like that.
Perfect, right?
And that works for this row, and for row 11, but it's not going to work for the other rows.
Alright, so just in really simple, plain English: "Hey Excel, if you get an N/A, just grab the previous value from this column.
Whatever you found above me, bring that down here." So, we come back up here and IFNA.
That's what they're testing.
IFNA is brand new in Excel 2013.
The guys that wrote the test want to make sure that you know that IFNA-- I know at the end of that, if we get an N/A-- then what I want is a value from just above me.
So, in this case, that's cell B1-- like that-- and we'll copy that down to all of our rows and paste.
Alright, so, the first one, the VLOOKUP works; the second one, the VLOOKUP doesn't work.
So, the IFNA says, "Just grab Afghanistan from right above me." And it goes all the way down, grabbing the value from just above me until it gets to the one that works; and then it switches, and switches, and switches, and switches.
Nothing hard here.
You were trying to make it too hard.
This test-- that I can't tell you the name of-- the test is never going to be that hard.
Look for the easy solution.
Mike?
Let's see what you have.
Mike: Thanks MrExcel!
Yes indeed, IFNA and VLOOKUP are the way to go.
Excel 2013 or later, I guess that's what the exam people are looking for.
Now, I'm going to go over to the next sheet and do something totally ridiculous.
This formula probably won't even be able to be understood by the people who grade your exam.
Now, what I'd like to do in this column, as I copy the formula down, is use a regular VLOOKUP, but I want, internally, inside the formula-- I want the formula to always get the last text item as I copy the formula down.
So when I get to this cell, and this cell, and this cell, there's the last text value; but, when I get to here, it needs to have that as the LOOKUP value-- and then boom, boom, boom-- the LOOKUP value still needs to look back to that AX.
And the way I'm going to do that is to use the original LOOKUP function.
This is the original function that Brickland and Franksten put in to the first version of Visi-Cal, way before VLOOKUP and HLOOKUP.
And the LOOKUP function only does approximate match Lookup; it doesn't do exact or approximate like VLOOKUP.
The advantage to Approximate Match Lookup is in the Lookup value.
If you put a value that's bigger than anything in the actual Array or Result Vector, it'll always get the last one.
So watch this.
For the LOOKUP value, I'm going to End double-quotes, and so I'm looking up text, type zzz.
Now, I'm using zzz because I know none of the ISO country codes will ever start with zzz.
So, this is going to be bigger than anything ever entered into this column.
Now, comma.
And I'm using the Array version...
I'm going to click on cell A2; colon; close parentheses; click on the first A2, hit the F4 key to lock it-- just the first cell reference.
Now, as I copy this down, that blue range will expand, since all of these cells are empty zzz... will always return AF because that's the last text item in the column.
Ctrl+Enter.
Notice that's a LOOKUP value as I copy down, now inside of our formula we actually have the correct LOOKUP value all the way down.
If we come down to some cell and hit F2, you can see there's the expandable range, and, sure enough, LOOKUP is getting the last text item entered.
Now I'm going to come to the top.
F2.
That whole thing is the LOOKUP value that I'm going to use inside of VLOOKUP.
Now I come to the end.
Comma; Table Array; I come on over to Lookup Table, highlight the table.
I'm up here in the formula bar; I hit F4, comma, 2, because the second column has the thing I need to get-- comma, 0-- that means exact match.
Closed parentheses; Ctrl+Enter; and I'm going to copy it all the way down.
And look at that.
Now, F2-- that's much too complicated.
That's a crazy formula when all you need is IFNA and VLOOKUP.
However, there is a situation-- I'm going to xls1 too, here-- there is a situation where this will get you into trouble.
And if I copy this down, notice it's all Afghanistan and look at that.
What is going on there?
Well, we actually-- if we put this into edit mode, there's an extra space there, so there's some situations where we actually want to see the N/A error.
So, if that was possibly the case, then we'd need to use a slightly different F2 logical test now.
I'm going to click on Value and copy that because that's the VLOOKUP, we're still going to use that; Ctrl+C; Escape.
And now I'm going to use a different logical test.
This is how we had to do it before Excel 2013: =IF(ISBLANK( Now, ISBLANK only will deliver a true when it sees a cell is totally empty.
=IF(ISBLANK(A2) If it is blank like it is down here-- =IF(ISBLANK(A2), -- then what do I want?
To look one cell above.
=IF(ISBLANK(A2),B1, Otherwise, Ctrl+V-- =IF(ISBLANK(A2),B1,VLOOKUP(A2,'Lookup Table'!$A$2:$B$26,2,False) --do the VLOOKUP.
=IF(ISBLANK(A2),B1,VLOOKUP(A2,'Lookup Table'!$A$2:$B$26,2,False)) Ctrl+Enter; double-click; and send it down.
So, in that case, we are prompted.
We know that there's a problem here.
Now I can come here; Backspace; Enter; and now everything works.
Now I'm going to copy it down.
Ctrl+Home.
That formula is a little bit longer than just IFNA-- we had to use three functions-- but, in some circumstances, you might want to take the extra time to create this longer formula than IFNA and VLOOKUP.
I'm sure on the test that either one of those will work, but this is the one you probably don't want to use.
Alright, I'm going to throw it back to MrExcel.
MrExcel: Well, hey, Mike, those were a couple of cool formulas, but again, I think the test was looking for the easy way-- the IFNA.
And like you said, if you actually put that formula in, they wouldn't have a clue that that would actually work.
I want to thank everyone for stopping by.
We'll see you all next time for another Dueling Excel Podcast from MrExcel and ExcelIsFun.
I'm Bill Jelen from MrExcel; I'll be joined by Mike Girvin from ExcelIsFun.
This is our episode 189: VLOOKUP from Outline Data.
Hey, I'm redacting parts of this question, but it's a great question: "Unfortunately, my friends took the-- "I can't tell you what"-- exam again and still had a problem with this question.
We have created a spreadsheet to mirror the question.
We are totally clueless on this formula.
Also, answer must be name of country, not true or false.
Most sincerely,--” and then, I redacted their name, but there's five of them who are trying to figure this out.
Alright, so here's the question: How to create a VLOOKUP when Column A has blanks.
I want to populate Column B with a country, and here's our Lookup Table, from Code to Country, like that.
For example, A2 will be Afghanistan, and that will repeat all the way down to 10.
And then we'll get to the new one.
AX will be the Aland Islands, A11 to A18.
The example of the expected results is here.
Alright.
Well, hey, I feel your pain, but you are over-thinking this.
Anything that's on that-- can't tell you the name of the exam-- is not going to be as hard as what you are making this out to be.
It has to be super simple: =VLOOKUP(A2, Go do a VLOOKUP in this Lookup Table, here...F4, 2, false...
=VLOOKUP(A2,'Lookup Table'!$A$2:$B$26,2,False).
Like that.
Perfect, right?
And that works for this row, and for row 11, but it's not going to work for the other rows.
Alright, so just in really simple, plain English: "Hey Excel, if you get an N/A, just grab the previous value from this column.
Whatever you found above me, bring that down here." So, we come back up here and IFNA.
That's what they're testing.
IFNA is brand new in Excel 2013.
The guys that wrote the test want to make sure that you know that IFNA-- I know at the end of that, if we get an N/A-- then what I want is a value from just above me.
So, in this case, that's cell B1-- like that-- and we'll copy that down to all of our rows and paste.
Alright, so, the first one, the VLOOKUP works; the second one, the VLOOKUP doesn't work.
So, the IFNA says, "Just grab Afghanistan from right above me." And it goes all the way down, grabbing the value from just above me until it gets to the one that works; and then it switches, and switches, and switches, and switches.
Nothing hard here.
You were trying to make it too hard.
This test-- that I can't tell you the name of-- the test is never going to be that hard.
Look for the easy solution.
Mike?
Let's see what you have.
Mike: Thanks MrExcel!
Yes indeed, IFNA and VLOOKUP are the way to go.
Excel 2013 or later, I guess that's what the exam people are looking for.
Now, I'm going to go over to the next sheet and do something totally ridiculous.
This formula probably won't even be able to be understood by the people who grade your exam.
Now, what I'd like to do in this column, as I copy the formula down, is use a regular VLOOKUP, but I want, internally, inside the formula-- I want the formula to always get the last text item as I copy the formula down.
So when I get to this cell, and this cell, and this cell, there's the last text value; but, when I get to here, it needs to have that as the LOOKUP value-- and then boom, boom, boom-- the LOOKUP value still needs to look back to that AX.
And the way I'm going to do that is to use the original LOOKUP function.
This is the original function that Brickland and Franksten put in to the first version of Visi-Cal, way before VLOOKUP and HLOOKUP.
And the LOOKUP function only does approximate match Lookup; it doesn't do exact or approximate like VLOOKUP.
The advantage to Approximate Match Lookup is in the Lookup value.
If you put a value that's bigger than anything in the actual Array or Result Vector, it'll always get the last one.
So watch this.
For the LOOKUP value, I'm going to End double-quotes, and so I'm looking up text, type zzz.
Now, I'm using zzz because I know none of the ISO country codes will ever start with zzz.
So, this is going to be bigger than anything ever entered into this column.
Now, comma.
And I'm using the Array version...
I'm going to click on cell A2; colon; close parentheses; click on the first A2, hit the F4 key to lock it-- just the first cell reference.
Now, as I copy this down, that blue range will expand, since all of these cells are empty zzz... will always return AF because that's the last text item in the column.
Ctrl+Enter.
Notice that's a LOOKUP value as I copy down, now inside of our formula we actually have the correct LOOKUP value all the way down.
If we come down to some cell and hit F2, you can see there's the expandable range, and, sure enough, LOOKUP is getting the last text item entered.
Now I'm going to come to the top.
F2.
That whole thing is the LOOKUP value that I'm going to use inside of VLOOKUP.
Now I come to the end.
Comma; Table Array; I come on over to Lookup Table, highlight the table.
I'm up here in the formula bar; I hit F4, comma, 2, because the second column has the thing I need to get-- comma, 0-- that means exact match.
Closed parentheses; Ctrl+Enter; and I'm going to copy it all the way down.
And look at that.
Now, F2-- that's much too complicated.
That's a crazy formula when all you need is IFNA and VLOOKUP.
However, there is a situation-- I'm going to xls1 too, here-- there is a situation where this will get you into trouble.
And if I copy this down, notice it's all Afghanistan and look at that.
What is going on there?
Well, we actually-- if we put this into edit mode, there's an extra space there, so there's some situations where we actually want to see the N/A error.
So, if that was possibly the case, then we'd need to use a slightly different F2 logical test now.
I'm going to click on Value and copy that because that's the VLOOKUP, we're still going to use that; Ctrl+C; Escape.
And now I'm going to use a different logical test.
This is how we had to do it before Excel 2013: =IF(ISBLANK( Now, ISBLANK only will deliver a true when it sees a cell is totally empty.
=IF(ISBLANK(A2) If it is blank like it is down here-- =IF(ISBLANK(A2), -- then what do I want?
To look one cell above.
=IF(ISBLANK(A2),B1, Otherwise, Ctrl+V-- =IF(ISBLANK(A2),B1,VLOOKUP(A2,'Lookup Table'!$A$2:$B$26,2,False) --do the VLOOKUP.
=IF(ISBLANK(A2),B1,VLOOKUP(A2,'Lookup Table'!$A$2:$B$26,2,False)) Ctrl+Enter; double-click; and send it down.
So, in that case, we are prompted.
We know that there's a problem here.
Now I can come here; Backspace; Enter; and now everything works.
Now I'm going to copy it down.
Ctrl+Home.
That formula is a little bit longer than just IFNA-- we had to use three functions-- but, in some circumstances, you might want to take the extra time to create this longer formula than IFNA and VLOOKUP.
I'm sure on the test that either one of those will work, but this is the one you probably don't want to use.
Alright, I'm going to throw it back to MrExcel.
MrExcel: Well, hey, Mike, those were a couple of cool formulas, but again, I think the test was looking for the easy way-- the IFNA.
And like you said, if you actually put that formula in, they wouldn't have a clue that that would actually work.
I want to thank everyone for stopping by.
We'll see you all next time for another Dueling Excel Podcast from MrExcel and ExcelIsFun.
