VLOOKUP from Outline Data

November 10, 2017 - by Bill Jelen

Someone taking an Excel certification test missed a question. They were trying to overthink the question. It doesn't require an insane array formula. A simple IFNA will solve it. While there are many ways to solve everything in Excel, sometimes the simplest ways go overlooked.

Watch Video

Auto-Generated Transcript

welcome back it's time for another
dueling excel podcast I'm Bill Jelen
from MrExcel I'll be joined by Mike
Gerber from Excel is one 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 that I
can't take what exam again and still had
a problem with this question we have
created a spreadsheet to mirror the
question we're totally clueless on this
formula the answer must be the name of
the country not true or false most
sincerely and that 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 column 1 how to
create a vlookup when column a has
blanks I want to populate column B with
the 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 new one ax will be
the OL Anandi Island 11 to 18 example of
the expected results is here alright
oh hey I feel your pain but you are
overthinking 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 equal vlookup go do if you
look up find a F in this lookup table
here F 4 comma 2 comma 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 equal if n a that's
what they're testing if n a is brand new
in Excel 2013
the guys that wrote the test want to
make sure you know the if n a I know at
the end of that if we get an n/a than
what I want is the value from just above
me so in this case that cell b1 like
that and we won't 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 if na
grab Afghanistan from above me and it
goes all the way down grabbing the value
from just above me until it gets 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
thanks MrExcel yes indeed if an a 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 gonna 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 on this cell there's the last text
value but when I get to here it needs to
have that as the look of that 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 Bricklin and
Frank stand put into the first version
of visie calc way before vlookup and H
lookup 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
I'm zze now I'm using zze because I know
none of the ISO
country codes will ever start with Z Z Z
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 gonna click on cell a2 colon
close parentheses click on the first a
to 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 Z Z Z will
always return a F because that's the
last text item in the column control
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 a vlookup now I come
to the end comma table array I come over
to a lookup table highlight the table
I've up here in the formula bar and hit
f4 comma 2 because the second column has
the thing I need to get come 0 that
means exact match close parentheses
control enter and I'm gonna 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 if
and a and vlookup however there is a
situation I'm gonna go over to XLS one
two 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 in edit
mode there's an extra space there so
there's some situations where we
actually want to see the n/a air 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 gonna use that ctrl
C escape and I'm going to use a
different logical test this is how we
had to do it before Excel 2013 is blank
now is blank only will deliver a true
when it sees a cell is totally empty
close parentheses if it is blank like it
is down here comma then what do I want
to look one cell above comma otherwise
control V do the vlookup close
parentheses control 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 and I'm gonna copy it down control
home that formula is a little bit longer
than just if an a we had to use three
functions but in some circumstances you
might want to take the extra time to
create this longer formula then if n/a
and vlookup I'm sure on the test that
either one of those will work but this
is the one you probably don't wanna use
all right I'm gonna throw it back to mr.
Excel well I ate Mike those were a
couple of cool formulas but again I
think the test was looking for the easy
way the if na and like you said if you
actually put that formula they wouldn't
have a clue that that would actually
work I want to thank everyone stopping
for stopping viable see you next time
for another dueling excel podcast from
MrExcel excel is fun

Download File

Download the sample file here: Duel189.xlsx

Title Photo: Kaz / Pixabay