Hi, my name's Mr. Hall.

Welcome to this Oak National Academy lesson, which is called Lookup and Reference Functions and is taken from the unit Spreadsheets.

Thank you for joining me for this lesson today.

I'm really looking forward to learning with you as we go through the lesson.

The outcome of this lesson is I can use lookup and if functions to retrieve data efficiently.

In this lesson, we've got four keywords.

So the first key word is database.

A database is an organised collection of data.

Then we have function.

A function is a built-in formula in a spreadsheet that helps you do calculations quickly.

Next we've got lookup.

Lookup is a function that looks for a value in one column and finds matching information from the same row in another column.

And finally, we have if.

So if is a function in a spreadsheet that checks if something is true or false.

And you'll notice the last two keywords lookup and if are capitalised.

That's deliberate.

That's because when you use those functions in a spreadsheet, they will be capitalised when you use them.

We've got two learning cycles in this lesson.

So first of all, you're going to implement a lookup function to retrieve data.

That's our first learning cycle.

And then secondly, you're going to implement an if function to give the user feedback.

So we'll move on to that later on.

So let's get started with that first learning cycle.

First a question.

So how do you use a self-service checkout in a shop? So Andeep says you find the barcode on the item you want to buy, and then scan it in.

And Lucas adds the checkout machine beeps when you scan in your shopping.

Have you ever thought about how a self-service checkout works? Let's look into that in a little more detail.

So what do you think is happening when the checkout machine beeps? And what happens after the beep? Well, all products in a shop have barcodes.

The barcodes hold data about each item, such as, in this case, we've got type of product, size, manufacturer, and country of origin.

Barcodes don't hold any data about the price.

Why do you think that might be? So barcodes don't hold price data.

A database is an organised collection of data.

The price is held in the shop's database, not the barcode.

This makes it easier to change the price to reflect special offers and price changes.

So all the things that are held in the barcode, things like manufacturer, size, country of origin, that kind of thing, that's all quite fixed data.

It's unlikely to change.

Things that might change and change quickly, such as price, are held in the database instead.

And we'll look at another example of that in a minute.

Time for a question.

In a shop, where is the price data of an item held? Is it A, the barcode, B, the item, C, the database, or D, the till? Well done.

C is the correct answer.

As you've just seen, the price data of an item is held in the database.

So have a look at this example of a receipt from a shop.

So loose items such as here get two lines on the receipt.

The first line is the item name.

So we've got Courgettes Loose, which is the item name, and then the second line is the weight of the item or how many individual items. So here we've got 0.

315 kilogrammes at two pounds per kilogramme, and that gives us a price of 63 pence.

Sometimes the name of the products are too long to fit on the till receipt.

So we've got some abbreviations.

So we have this one here, which is JMB KNG PWN, and you might be able to work out that just from the abbreviation.

That actually means jumbo king prawns.

So the database will have the shortened and the original names in it.

So when the checkout machine beeps, it signals a process where the stock level in the database is changed.

This is another example of something which changes frequently.

So the stock level changes each time an item is bought, so that's held in the database.

The price of the item is also retrieved from the database.

It's something that can change regularly.

At the same time, the sales figures are updated and an automated recording process starts.

In a spreadsheet, there are functions to retrieve data in a similar way to how a shop checkout machine retrieves data from a shop database.

You'll use the lookup function in a spreadsheet to retrieve data from one sheet and use it in another sheet.

So, open the spreadsheet Rockstar merchandise, the Rockstar Challenge team have created a merchandise database using a spreadsheet.

There are two sheets.

The first sheet is the merchandise database.

So we've got here the first sheet, so there's a column with item number, and item numbers going from 1001001 through to 1001016, and then the remaining stock of each of those items on each row.

We've got some other columns, Short name, Selling price VAT, VAT, paid, and Status.

Those are not populated at the moment.

So make sure you've got that spreadsheet open.

The second sheet is the lookup sheet.

It contains all the information needed to fill out the first sheet.

So we've got one column that looks fairly similar to the first one.

So we have item number, and the rest look a little bit different.

Let's see why that is.

So looking at the first sheet more closely, each entry in the item number column is a unique number, similar to a barcode.

So each row there, rows 8, 9, 10, 11, have a unique item number.

So the first one ends in one, and then it continues through to four for the row 11.

To retrieve the short name for the product from the lookup sheet, you use this function.

Now, this is quite a long function, but don't worry, we'll break it down into all the parts, so you'll find out what each bit meet.

So the function is =VLOOKUP (A8, 'Lookup sheet' !$A$2:$E$17,3,FALSE).

So as I said, that looks like quite a long and difficult function, but we'll break that down into all the smaller parts, and it'll make it much easier for you to understand.

So let's get started with that.

So the first bit VLOOKUP is a vertical lookup, which means it searches through the first column vertically.

So that's the VLOOKUP bit.

Then we've got A8, which is the cell that's being looked up.

Then we have this bit highlighted, 'Lookup sheet!' !$A$2:$E$17.

So this is the name of the sheet and the range of cells being checked.

Make sure to use apostrophes around the name of the sheet, so that's the bit around Lookup sheets.

So that's that part highlighted in blue, and it refers to the part of the spreadsheet highlighted in blue below.

The dollar signs are absolute sell references.

So that means they'll stay locked even when the function is replicated.

So $A$2:$E$17, if you replicate that function, they will stay the same because of those dollar signs.

So the number three is the instruction to return the value in the third column.

So the third column, column C is ItemName_Short.

So that will return the value in the third column.

And then finally, FALSE is used to make sure it searches for an exact match.

So we don't want a close match to column C.

We want an exact match.

Now have a go at filling in the blanks of this sentence.

We have the function there that we've just gone through at the top.

So in this VLOOKUP blank, the 3 returns the blank from the blank column of the blank.

And you've got the words LOOKUP range, function, third, and value to fill in those blanks.

So in this VLOOKUP function, the 3 returns the value from the third column of the LOOKUP range.

So reading the whole sentence, in this VLOOKUP function, the 3 returns the value from the third column of the LOOKUP range.

So and Andeep's identified that this function searches for the given range for a value exactly matching the item number in cell 8.

And Lucas says, "Right.

And when it finds it, it retrieves the data in the third column aligned with it." So one more time going through that function at the bottom, so =VLOOKUP (A8, 'Lookup sheet' !$A$2:$E$17,3,FALSE).

As you're using an absolute cell reference marked by the dollar symbols in your function, once it's correct, you can use the fill handle tool to drag the function down a column to fill it all in.

Before you do that, follow Lucas's advice and make sure you check what's being filled in is correct.

You make a mistake in the top row and then you copy it down to all the other roads, your mistake will be replicated.

So just make sure that you've got your function spot on before you copy it down.

Time for task A.

So open the spreadsheet, Rockstar merchandise, and also open the sheet Merchandise database.

And your first task is, in column B, use the VLOOKUP function to retrieve the shortname data from the LOOKUP sheet.

So this is what you should use.

So =VLOOKUP.

So A8, that's the cell to lookup.

And then in apostrophes, Lookup sheet.

In this range of cells, that means, and then you've got the.

To return the value, in the third column, you use number 3.

And to search for an exact match, you use false.

So this is the function you need.

It's =VLOOKUP (A8, 'Lookup sheet' !$A$2:$E$17,3,FALSE).

And that will return the short name from the lookup sheet in the Short name column, which you can see there.

So for the next part of the task, in column C, use the VLOOKUP function to retrieve the selling price data from the LOOKUP sheet.

You'll need to change your existing VLOOKUP function to retrieve the data from a different column, You'll need to use =VLOOKUP (A8, 'Lookup sheet' !$A$2:$E$17,4,FALSE) So it's the same function you used before, but this time we've got the number 4, which has got the blue box around it, instead of the number 3.

So it's looking at the fourth column, rather than the third column, and that's filled in the selling price, including that column in your spreadsheet.

So with that, we can move on to the second learning cycle, which is to implement an IF function to give the user feedback.

So stock levels are also tracked in the RCS merchandise database.

And we've seen that before, so we've got in the remaining stock column you can see there stock levels for each of the products.

Why do you think a shop might want to track stock levels in a spreadsheet? And what problems might that be if stock runs out and no one notices? And how could you design a spreadsheet to help you spot when stock is getting low? So what decisions do you make based on conditions? So Andeep gives an example.

"If it's raining, then I take my umbrella with me.

If it's sunny, then I do not take my umbrella." And Sam says, "If the time is 7:00 AM or later, then I get out of bed.

Otherwise, I stay in bed." And it's similar in spreadsheets.

An IF function checks whether something is true or false, and then chooses what to show.

When we use an IF statement, we're using a question that the IF function can answer with true, which is yes, the condition is met, or false, which means no, the condition is not met.

In spreadsheets, an IF function checks whether something is true or false, and then chooses what to show.

This gives the user feedback on the data in the sheet.

So here's an example.

If remaining stock is less than 100,, output reorder, else output nothing.

So that's a really good example of if function can help in a spreadsheet.

So that means once the stock gets to under 100, there'll be an instruction to reorder.

So let's have a look at that.

So the function here is =IF (E8<100,"Reorder","").

So running that function, you can see that Reorder has come up as a status in the item number 1001001 for baseball cap, and we've got a remaining stock of 73.

So that function has returned Reorder in the status column 'cause it's less than 100 remaining stock.

So the IF function is.

there's the function again, =IF (E8<100,"Reorder","").

Again, that could look a little bit difficult to understand at the moment.

So let's break that down into the parts of the function.

So first of all, E8<100, That's the condition we want to check.

So if cell E8 is less than 100, that's what we want to show if the condition is true.

So in the quote marks, we want to say Reorder if it's true.

And then after the comma, what we want to show if the condition is false, let's just quote quote with nothing in between.

So we don't wanna show it anything if the condition is false.

So look carefully at the quotation marks and how they are placed.

It's really important you get those in the right place.

So let's look at that part again.

So the first set of quotation marks have Reorder between them, then there's a comma, and then two quotation marks with nothing in between next to each other.

Let's have a look at the true or false question.

In an IF function, the part inside the quotation marks is the condition.

Is that true or false? Well done, it's false.

And it's false because the condition is outside the quotation marks.

The text to display is in the quote.

So as we saw, we wanted to display Reorder if it's true and nothing if it's false.

Conditional formatting can format cells for you as long as something in the cell meets a condition.

Conditional formatting can be used to enhance the effect of the IF function in the status column.

So rather than just having Reorder in that column, which might not stand out enough, we can use conditional formatting.

So select the cells you want to format first.

So you want to format all the sales in the column, which is Status.

Then click on Format.

And third, click on Conditional formatting from the list.

And you can set the format rules and formatting styles here.

So in this example, if the sale text is exactly Reorder, then the formatting will be applied, and the formatting there is for the cell to be highlighted in orange to make it stand out.

So there we have it.

So conditional formatting makes the IF function more effective and gives the user additional feedback on the sheet data.

So there's no missing there that the various different rows you need to reorder.

You can see that the cells which have Reorder in are on the rows where the stock is less than 73.

So the function is doing its job there.

And the orange highlighting made it really easy to pick out those rows.

Time for a question.

Why would you combine an IF function with conditional formatting? Is it A, to sort data into the correct order, B, to hide empty cells in your sheet, C, to make important results stand out visually, or D, to enter text in faster? Well done, the correct answer is C.

It's to make important results stand out more visually.

Time for task B.

So open the spreadsheet Rockstar merchandise.

And first, in the LOOKUP sheet, use a formula to complete column G, the VAT.

The VAT rate can be found in cell I1.

Then when you've done that, in the merchandise database, column D, use a VLOOKUP function to retrieve the VAT pay data from the LOOKUP sheet.

So here's the formula you need to calculate the VAT.

It's =D2*$I$1.

Because that's an absolute reference, we don't want it to change.

And then the second one, the function to retrieve the VAT paid data is =VLOOKUP (A8, 'Lookup sheet' !$A$2:$E$17,5,FALSE).

The third task, in the LOOKUP sheet, use an IF function to complete column F, the status.

The status should show the user a message if the remaining stock is under 100.

And then four, use conditional formatting in column F to emphasise the user message.

So the third task, here's the function to show Reorder.

if the stock is below 100.

It's =IF(E8<100,"Reorder","").

And then the fourth task to set the conditional formatting rules, so you want to apply a single colour to range F8 to F23.

In this example, we've said if the text is exactly Reorder, so that should be the same on yours, and then whatever colour you've chosen to format your cell.

So in this case we've chosen a fill colour of orange.

You can open the spreadsheet Rockstar merchandise solution to see the working solution.

Here's a summary of today's lesson.

The VLOOKUP function is used to retrieve data from another sheet based on a matching value.

LOOKUP and reference functions can be useful for working with sets of data efficiently.

Data can be stored in a database which is an organised collection of data.

The if function checks a condition and returns different outcomes, depending on whether the condition is true or false.

The if function is used to give the user feedback in a spreadsheet.

Thank you for joining me for today's lesson and learning along with me.

I look forward to seeing you again soon.