Practice Video: Dynamic Lookups: INDEX, MATCH

Loading...

Do curso por Macquarie University

Excel Skills for Business: Intermediate II

628 classificações

Explore Related Videos

At Coursera, you will find the best lectures in the world. Here are some of our personalized recommendations for you

Macquarie University

Excel Skills for Business: Intermediate II

628 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

Alex has just one field left to calculate before his template is complete,

and that is the item discount.

Discount is calculated using two parameters however,

the item category discount and the customer category.

To get the discount amount, Alex is going to need to look these both up in

the discount matrix on the Discount Matrix tab.

This discount matrix shows the percentage discount for

each combination of discount category and customer category.

So for example, if your Discount Category is C, and

your Customer is Silver they will get 7.5%.

Now the VLOOKUP is ideally suited for looking up a single value.

But here we're trying to lookup two values, so VLOOKUP not the best choice.

We could try a combination VLOOKUP and HLOOKUP, but it's going to make for

a very clumsy and inefficient calculation.

So instead, we're going to look at a new function called the INDEX function.

This is another very powerful and versatile lookup function and

ideally suited to solving this kind of problem.

Let's see how it works.

So we're going to click on E11 and type =in and Tab to select INDEX.

The first argument that requires is the lookup array.

This can be a single column or a single row or an entire matrix.

In this case, we need the entire matrix, so I'm going to select that.

You'll notice Alex is actually called this discounts, so next time,

we'll use the name range and type comma.

The next argument the INDEX requires is the row number.

Now, if we have a look at our discount category C, it's the third row.

So we're just going to type a 3 and type a comma.

The third argument is optional.

If you only have a single column,

you don't need to specify that it's column one.

But we have four columns here, and Silver is in column two.

So we're going to type the 2, close our brackets and

we will click Enter, that's our 7.5%.

So in simplest that is how the INDEX works.

But what we did here was very contrived, by the time we've actually looked up

the row and looked up the column, we've done most of the hard work.

So really we need Excel to automate that process for us as well,

and to do that we're going to need to use the MATCH function.

And typically, you see INDEX and MATCH working together.

So what we're going to do now is replace this 3 that we had to lookup ourselves and

type in with a MATCH.

So I'm going to delete that, type m for MATCH and Tab.

Our lookup value this time is our discount category, so

I'm going to click on the C and type a comma.

And we're going to look that up in this little column over here of discount

categories.

Again, note this range has been named and type a comma and

it is an exact MATCH, so 0 and close our brackets.

So that's our first MATCH that we'll look up our row number.

Now, let's do exactly the same for our column number.

So remove the 2, select MATCH,

we're going to look up the value Silver in this little row over here.

And it's an exact MATCH close your brackets for your MATCH, so

that you end up with a black bracket at the end and click Enter.

And once again get the 7.5%, but now if we change our

Customer Category to Gold, it updates our Discount Value.

So that is the INDEX MATCH,

we're now going to apply exactly that logic to our customer quote.

So we're now going to click back into our Customer Quote and

calculate the Discount for each of these items using our INDEX MATCH.

While the Discount Category will vary for each of the items,

the Customer Category remains the same.

So rather than having to do that MATCH 60 times, we're just going to do it the once.

So start by clicking into I4 and type = m and Tab for MATCH.

Our lookup value is our Customer Category, so we're going to click on that.

And our lookup array is our Customer Categories, so

we're just going to type cu and choose Customer Categories.

And then of course make it an exact MATCH, close your brackets and

if you remember Gold was in column three, so when we click Enter.

There we go, column three.

We now need to do the rest of our INDEX MATCH.

So I'm going to click into H6 and

we're going to type = in for INDEX and Tab.

You will recall, our lookup array was called Discounts.

So I'm going to type disc and select Discounts and then type comma.

Now to get our correct row, we're going to need to look up our Discount Category.

So we're going to do a MATCH, and we're going to look up

the Siscount Category in our Discount Category list.

And again, that's an exact MATCH, so put your 0,

close your brackets and type another comma.

The column, we've already looked up that was our column for Gold.

So we're going to click into I4, and

then don't forget to press F4 to apply your dollars.

Close your brackets for your INDEX, and now when we click Enter,

we get our Discounts for each of those items.

Not only that, but if we were to upgrade our customer to Platinum,

you can see all the discounts update accordingly.

The index match is a little more complex than the VLOOKUP.

However, it does have certain advantages.

We've seen how we can look up both a row and a column simultaneously.

The other benefit is unlike the VLOOKUP where your lookup column

must sit to the left of the value you want to return.

The INDEX MATCH has no such restriction, so it's a little more versatile.

The INDEX MATCH also has the advantage that you can split out

the look up from the return.

In other words,

you can split the match from the INDEX where you can't do that with the VLOOKUP.

And so if you were trying to look up multiple values in the same row, for

example, by creating an INDEX MATCh and splitting out the match part.

You can create a far more efficient formula as well.

All these lookup functions take a little practice to get used to.

So don't forget to do this week's practice challenge, and

take some time to see how you could start applying these different LOOKUP functions

to solving your problems in the workplace.

[SOUND]

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.

© 2019 Coursera Inc. Todos os direitos reservados.

Baixar na App Store

Baixar no Google Play