Practice Video: Approximate Matches: Range VLOOKUP

Loading...

Do curso por Macquarie University

Excel Skills for Business: Intermediate II

415 classificações

Macquarie University

Excel Skills for Business: Intermediate II

415 classificações

Curso 3 de 4 em Specialization Excel Skills for Business

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces across the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. In this third course of our Excel specialization Excel Skills for Business you will delve deeper into some of the most powerful features Excel has to offer. When you have successfully completed the course you will be able to Check for and prevent errors in spreadsheets; Create powerful automation in spreadsheets; Apply advanced formulas and conditional logic to help make informed business decisions; and Create spreadsheets that help forecast and model data. Once again, we have brought together a great teaching team that will be with you every step of the way. Nicky, Prashan and myself will guide you through each week. As we are exploring these more advanced topics, we are following Alex who is an Excel consultant called in by businesses that experience issues with their spreadsheets.

Na lição

Automating Lookups

How do find information from different parts of a workbook? This module introduces you to functions like CHOOSE, VLOOKUP, INDEX, MATCH and other dynamic lookups to find and display data from several sources.

Practice Video: Introduction to Lookups: CHOOSE3:45

Practice Video: Approximate Matches: Range VLOOKUP5:17

Practice Video: Exact Matches: Exact Match VLOOKUP6:40

Practice Video: Finding a Position: MATCH7:28

Practice Video: Dynamic Lookups: INDEX, MATCH7:06

Week 3 Wrap-Up1:28

Conheça os instrutores

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

Before Alex can continue work on the customer quote,

there's one additional field he needs to correct on the price list.

So we're going to hop onto the Price List tab

and you'll see our Discount Category is missing.

Now the Discount Category is calculated per item based on the Retail Price.

So if the price is between $0 and $1,000, it's Category A,

$1,000-$10,000 is Category B, etcetera.

Now rather than going in manually entering all these categories,

Alex wants to automate the process of looking them up.

And a function ideally suited to this task is the VLOOKUP.

VLOOKUP is a very versatile powerful function and is used extensively in business.

The way the VLOOKUP works is, given a value,

like the Retail Price,

it will go and try and match it into a master dataset.

When it finds a match,

It will then return a corresponding value from the same row.

Now VLOOKUP is quite a strange name.

The V means vertical.

And the VLOOKUP can only be used on lookup data that is organized vertically.

If your lookup data is organized horizontally,

then you need to use the HLOOKUP function. It's identical

and because most of our data sets are actually vertical,

you're more often going to use and encounter, the VLOOKUP.

The VLOOKUP also has two slight variations —

we have a range lookup and an exact match.

The exact match is ideal for things like bank account numbers and

employee IDs where you want to find exactly that value.

But in this case, we don't have an exact match for most of these values.

So I don't have a 995.

Instead, I want to find it within the range $0 up to $1,000.

And that would return me an A.

So this is called a range lookup.

And that's what we're going to be looking at first.

One thing to be aware of with the range lookup,

is your lookup data must be organized smallest to largest.

Right. Let's see how it works.

So I'm going to click into E4.

In fact, I'm going to make this column a little bit wider so you can see what I'm doing.

And then, I'm going to click into E4 and type equals VL and press Tab.

Now with these more complex functions,

take advantage of these little prompts to

help you remember the sequence of the arguments.

And the first one the VLOOKUP wants is our lookup value.

That's the piece of information we know that we're going to use to lookup something else.

So that's my Retail Price and I'm going to click on that and type comma.

The next argument is the table or array.

Now that's our lookup data set.

And in this case,

it's this little block of data over here.

Now with the VLOOKUP, you select the entire lookup data block,

not just a column or row.

But don't include the headings as they're not part of the data.

Now before we go any further,

when we copy the data down we don't want our lookup range moving.

So it's very important that we immediately make it absolute by pressing F4.

If you come back and do this later,

you run the risk of only putting the dollars on the H7 or on the G4.

So please make sure you apply it to both sides of the range.

Right. Now another comma.

The third argument is our column or index number.

When you do a VLOOKUP,

it's always going to match the lookup value to the first column.

But, you then need to tell it which of

the other columns to retrieve the corresponding value from,

because in many datasets you may have more than two columns.

In this case, we've only got two,

so we're going to type a 2.

Note I'm not putting an H. The VLOOKUP has no awareness of the rest of the worksheet.

It just looks at this little dataset,

and so, it's the second column.

Now the fourth argument,

you will notice is in square brackets.

That means, in some cases it is optional and in the case of the range lookup,

it is completely optional.

We can ignore it altogether.

So I'm simply going to close my bracket and click Enter.

And as we would expect,

our first item is Category A.

If I come back onto that cell and double click my fill handle,

you can see it has correctly categorized all the rest of my items.

So the range look up is ideal for when you need to categorize data.

That might be looking up tax tables or grading or in this case, the discount category.

In the next video,

we're going to look at the slight variation which is the exact match VLOOKUP.

Make sure you're confident with this one though before you move on.

Explore nosso catálogo

Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.

O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.

© 2018 Coursera Inc. Todos os direitos reservados.

Baixar na App Store

Baixar no Google Play