Hello again and welcome back to our course on Excel 2016 Advanced.
In this section we’re going to take a look at LOOKUP and Reference functions.
And in particular we’re going to start looking at LOOKUP functions.
And as the name implies what we do with a LOOKUP function is looking up a value in some
kind of index or a list and using the returned or identified value.
Now I’m going to demonstrate first a very, very simple LOOKUP function which is the Choose
function.
It’s really quite limited in what you can do but it’s a very easy function to use
and you may well come across situations where it does the job for you.
So by way of a very straightforward example of this let’s suppose that I have some responses
to some survey types of question.
And people give a numeric response in the range one to five.
And each of those numbers corresponds to a phrase.
And in a particular situation I want to not only show the response as a number but also
the phrase that it’s equivalent to.
Now the way that I can do that in this case, I’m going to put the phases in column D
is to use the Choose function.
Now I can either start typing =CHOOSE or I can go to LOOKUP and Reference and there is
the Choose function.
And first of all, first argument, is the index number.
So this is the number that we are going to look at and use as the basis for our choice.
Now for D3 the number I’m going to be looking at is the number in C3.
The next argument is the value I’m going to put into D3 if the value in C3 is one.
It always starts at one.
So I’m going to say that one corresponds to strongly agree.
Now I put in the next value.
A value of two corresponds to agree.
A value of three.
Now I can have up to 254 values there.
So at the moment I put in five values, which means in this case that I can accommodate
numerical values in C3 from one to five.
Now all the time that there is nothing there then I’m going to get an error message,
in this case #VALUE!.
I’ll come back to that in just a moment.
Let me now put a number in there, 2.
That’s agree, 4.
Now as you can see that’s pretty straightforward.
Now Choose always works with positive integer values starting at one on a continuous scale.
If in fact in the response there I put something like 3.6 what Excel 2016 does is to round
that down and it will give me the equivalent of if I’d put three in there.
I need that to be a little bit wider.
So that’s neither agree nor disagree.
And if it comes out below one, so let’s suppose I put 0.8 in there I’ll get an error.
And if I put a value of six in there of course I’ll also get an error.
So that’s basically how the Choose function works.
But it also presents us with another issue.
If I’m setting up something like this is there some way that I can avoid getting error
values like that one?
Now there are a couple of really good ways that you can deal with situations like this
one where, for example, you want to put a formula into several cells but you know that
until some other cells have values in them the formula is actually going to return an
error.
So for instance here you could actually test to see if the response was in the range one
to five.
And if it isn’t you could actually prompt the user to enter a value there.
But a very straightforward way of dealing with this is to use the IFERROR function.
Now with the IFERROR function what I do is in this case I evaluate my formula using Choose
and if there is no error I will get the value that Choose is going to deliver to me.
But if I get an error what I do is the second argument in the IFERROR function.
Note there the screen tip value IFERROR I can say what I’d like to appear in this
case in D3 if there’s an error.
Now I might want to put a message in there such as Response must be in the range one
to five.
Or if I’m anticipating that I’m going to fill D3 down to D12 with this formula and
therefore I’d get that message everywhere I might just choose at this stage to just
leave the D column entry empty.
So I could say I’m just going to put a space in there if there’s an error.
So I click on tick and now what I get is nothing.
Let’s fill the formula down.
I don’t get anything anywhere.
Let’s put a three in there.
Let’s put a one in there.
Let’s put a zero in there, etcetera.
Now as I say you may want to combine this with a more complex arrangement whereby you
say well if in fact there’s a response in the cell in column C that it’s not in the
range one to five then I actually want to say to the user You must enter a value in
the range one to five.
But as you can see you can build this up to be as complex as you need it to be.
But the IFERROR function is useful and the Choose function certainly can be as well.
So just to quickly run over that again.
Looking at D4, note the use of the Choose function there.
My index value is the value in C4 and the Choose function will return values for an
index value of one, two, three, four, up to the number of alternative values I specify.
And in the Choose function there I specified five potential values which must be one to
five.
They must go in sequence like that up to a maximum of 254.
Having said that an index value of 1 in C4 means that the value in D4 is strongly agree.
An index value of two would make it agree, an index value of three of course made it
neither agree nor disagree.
Now the five options there are all text values but in fact I could have put numbers in there,
I could have put cell references, I could have put functions, formulas, names.
So there are many, many possibilities of what you can put in this case into D4.
But it’s important to remember those limitations.
Now we’re next going to look at one of the most popular LOOKUP functions and actually
there’s a pair, HLOOKUP and VLOOKUP.
And in this section we’re going to have a very quick look at a simple example of HLOOKUP.
The H there stands for Horizontal Lookup.
And then in the next section we’re going to look at a more complex example of VLOOKUP
or Vertical Lookup.
So what we have here are some class scores in a quiz and I have scores for around 20
students, 19 in fact.
And what I want to do is to determine which quartile each student’s score puts them
in.
Now the lowest score there for Parker, Hector in row 12 is a 43.
We’ve got some very high scores as well.
We’ve got a 98 and so on.
So what I’m going to do first of all is to use a straightforward statistical function
to calculate the quartiles.
Now let me just explain what quartiles are.
If you have a set of results like this think of slicing them up into four even bits.
So 19 students, let’s call it 20 roughly.
You’re going to say what range of scores covers the first five, then the next five,
then the next five, and then the next five.
So you’re really dividing the class up into four quarters.
Now the first thing that I need to do here is to evaluate the quartiles.
These are the values which divide my results into four even sets.
Now if you have not used Quartile functions before let me just demonstrate something I
mentioned earlier on in the course.
You know you need a statistical function.
You’re not really quite sure what statistical function it is.
So you might start to type in Quartile, see maybe somewhere in that lot is quartile.
Look, there’s a whole set of them.
The first two, Quartile and Quartile.INC are effectively the same.
Quartile is the original Quartile function and Quartile.INC replaces it because there
is a distinction between Quartile.INC and Quartile.EXC.
Now I’m not going to go into that distinction here.
It is an important distinction and it’s to do with the use to which a quartile is
being put.
But for the purposes of demonstrating Lookup functions we’re going to stick quite comfortably
with Quartile.INC.
So we’re going to use that function.
And we have two arguments.
First of all which array of numbers are we going to find the quartile of?
So the array of scores there which is B2 to B20.
And I want the first quartile.
So this will be the score below which the first quarter of students would be positioned.
So this is if you like the weakest quarter in the class.
Click on OK and I find that score is 72.5.
Now what I need to be able to do is to evaluate the other quartiles.
And I’m going to arrange them horizontally.
Now if I select the three cells to the right and then use the Control-R keyboard shortcut
you notice that I get errors.
And the reason I get errors of course is because if I look at the formula in G2 I am now looking
at the scores in C2 to C20 which is definitely wrong.
So of course when I specify the array from which the quartile scores are going to be
calculated I need to use absolute references.
So let me undo that, go back to this formula and change it to $B$20.
Okay.
Now I can right fill.
Of course I’ll get the first quartile in all of those cells.
All then I’ve got to do is go in and edit those to get the second and subsequent quartiles.
And very importantly at the beginning of my set of scores is the score of zero.
Now the function we’re going to use in order to decide which quartile each score is in
is the HLOOKUP function.
And the way that the HLOOKUP function will work is that I will tell it where this list
is and for each student’s score, so let’s start with John Beale, 82.
It will read along that list horizontally. 82 are bigger than zero.
82 yeah bigger than that.
Yep bigger than 76.
No, I’m not bigger than 87.
And therefore the score of 76 in this case denotes the start of the third quartile.
The score of 72.5 denotes the start of the second quartile and a zero will denote the
start of the first quartile.
Now that just leaves the last score, 98.
Now that’s the highest score and the highest score is going to be in the fourth quartile
as well.
So basically as HLOOKUP works its way along there it waits until it gets to a score that’s
too big, in this case for John that would be 87 and says okay well John must be in the
category that begins with a score of 76 and that’s the third quartile.
That’s the quartile that starts at the median.
So we’re all set to work out these quartiles.
We’re going to use the HLOOKUP function.
The first argument is the value we’re going to look up.
Well the quartile C2 is going to be based on the score in B2.
So the lookup value is B2.
The table array that we’re going to look in is E2 to I3.
Now we’re going to need to be a little bit careful there because we know that we’re
going to need to absolutely reference that.
The row, which row gives us the value we want?
Well it’s the second row, the second row contains the quartile numbers.
And then we have a logical value range lookup.
Now if range lookup is set to true that means we’re looking for values in ranges.
So for quartile one it’ll be in the range zero to 72.5.
If we wanted to match the values exactly, so if we were looking for exactly 72.5, 76,
etcetera we would set range lookup to false.
But we want it to be true.
We’re looking at scores within ranges so that’s true.
Click on OK.
And it correctly says that John Beale is in quartile three.
Let’s do a fill down now and see where we get to with the other scores.
I’m just going to center those and I think they all look correct.
I suggest you check those yourself.
I’ll see you in the next section.