This week, Alex is going to be helping a network manufacturing company,
develop a template to speed up the process of developing customer quotes.
Work is already well underway.
Alex has already created a sheet to hold the current price list,
and he's created another sheet to contain the quote itself.
This contains a table, some data validation, and
some conditional formatting.
What the customer is looking for
is that the sales engineers can enter the minimum amount of information.
So just the item code, the location code and the quantity, and
have all the rest of the information automatically populated.
To do this, Alex is going to use some of Excel's LOOKUP functions.
And that's what we're going to be focusing on this week.
Now, the first piece of information we're concerned with is a description
of the installation location.
The sales engineer is going to simply enter a code 1 through 5.
And what we need to do is get one of these descriptions to populate automatically.
So number one is building core, number two is data center, etc.
Now, a function that can help us with this is the CHOOSE function.
CHOOSE works by retrieving a value from a list,
based on a given numeric value.
And that's exactly what we have here.
We have a number describing our location code, and
we're going to use that to retrieve one of these values based on position.
Let's watch it in action.
So we're going to click into C6 and type =CHO and then press Tab.
The first value the CHOOSE is asking for is an index number.
That is the numeric value that's going to provide the position within the list.
And in this case, it's our Location Code, so we're going to click on that.
Note the referencing, that's because we're working in a table.
Now, I type comma.
After that, we have to specify our list.
This can be up to 254 items, and you can either type it in or
click on values in your workbook.
We have all the values in the worksheet so
I'm going to click on building core, data center, fiber distribution,
main flows and finally, remote office buildings.
Make sure that your comma separate them,
but don't put an additional comma at the end.
Now, I also need to make this absolute, so
I'm going to just select all of them and press my F4 key.
The one thing to be aware of is you can't just select K6 to K10.
You have to individually list each list item.
All right, I can now close my brackets.
And when I click Enter, because it's a table,
this formula is going to automatically copy down.
And there we go, we now have the correct installation location description to
each of our different locations.
And a little bit of conditional formatting to divide the data and make it look nice.
So the CHOOSE function is a good choice.
When you need to do a LOOKUP from a very finite list,
that is not likely to change very often.
We can see in the moment though that we have much more complex problems we
need to solve.
And the CHOOSE function is not going to be the right LOOKUP function for these.
So in the next few videos,
we're going to look at some even more powerful and versatile LOOKUP functions.
[SOUND]