VLOOKUP from Outline Data


November 10, 2017 - by

VLOOKUP from Outline Data

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