2 Variable Lookup

Loading...

来自 PwC 的课程

Problem Solving with Excel

2824 个评分

PwC

Problem Solving with Excel

2824 个评分

课程 2（共 5 门，Specialization Data Analysis and Presentation Skills: the PwC Approach）

This course explores Excel as a tool for solving business problems. In this course you will learn the basic functions of excel through guided demonstration. Each week you will build on your excel skills and be provided an opportunity to practice what you’ve learned. Finally, you will have a chance to put your knowledge to work in a final project. Please note, the content in this course was developed using a Windows version of Excel 2013. This course was created by PricewaterhouseCoopers LLP with an address at 300 Madison Avenue, New York, New York, 10017.

从本节课中

More Advanced Formulas

In this module you will learn more advanced Excel formulas. We will show you how to create statistical formulas, perform an index match, and lastly, build financial formulas. We will provide you with an opportunity to problem solve using statistical formulas. Finally, we will give you an opportunity to practice what you have learned through a final project.

Index Match2:56

Index Match: MATCH Formula2:06

x and y Variables3:39

2 Variable Lookup6:01

与讲师见面

Alex Mannella
Alumni / Former Principal

[MUSIC]

As discussed in the introduction, INDEX MATCH

can be used to look up information in a grid with both an X and Y variable.

We will utilize the optional column reference

which we have previously defaulted to one in prior exercises.

Let's look at our new data set to get better understanding of

why this would be useful.

Our data set has names across the top, and personal identification going down.

This would mean the names would be an X input or column input,

and the personal identifiers would be the Y input or row input.

In this exercise, we are looking for personal information about Rod.

Why don't we first look up where in the data set the information for Rod is.

We're going to start with the MATCH formula.

So let's do =MATCH, followed by the lookup value.

Here it is, his name.

We are going to lock this cell, because it's the same for all the attributes.

The lookup array is a list of names across the top in our data set.

You'll see that the name range inserted itself.

And we will use a match type of 0, as we're looking for an exact match.

If we hit Enter,

we find out that Rod's information is in the fourth column of the data set.

And now we can copy and paste our formula down the remaining attributes,

since Rod will be in the fourth column for all answers.

Now we're going to use the MATCH formula to look up where the eye color is,

in the personal attribute field.

We are going to search for eye color using MATCH.

Lookup value is going to be eye color,

and our data set is going to be the personal information.

The name range pops up, and the MATCH type will be exact.

Ensure to lock the rows and columns where required.

I find that many formula issues are a result of missed cell locking.

If we copy this down for each cell, it's telling us what position,

weight, height, sex, and eye color are located in.

We can combine both the x and y MATCH in an index formula to find

the corresponding x and y coordinates for Rod's personal information.

We will do =index, and this time we select the entire data set.

We need to select the entire data set since, we're going to utilize both the row

and column lookup, and need the entire range of outcomes to work with.

Let's start at B38 and select down to

I44.

I'm going to lock the data we just selected as we've done throughout this

exercise, so it does not shift on me when I copy it.

Now I am looking at the row number.

We already looked up the row number for eye color in D147,

so let's input that here.

Similarly, the column number can be found in C147.

If I close the formula, hit Enter, we get brown.

Now, if I copy this down, we now have Rod's eye color, sex, height, and weight.

We just created a look up formula utilizing two variables,

however used three formulas.

Just like before, we do not need to do this in two separate steps.

We can do this all at once by replacing the row and

column inputs with the match formula that we wrote in columns C and D.

Let's start by typing =index,

and select our data set,

making sure to lock it.

For the row number,

we are going to use the match formula we were working with before.

The lookup value is going to be eye color that is saved within the personal info,

and the match type is exact.

We close the first match with the parenthesis, and then hit comma.

Notice that Excel is now asking us for the column number.

We are going to do the same thing we did before to look up Rod's column position.

We will use MATCH again,

but this time, we are looking for Rod.

This reference is anchored, so we're going to lock both the row number and

column so it doesn't shift on us.

The lookup array is name,

and the match is exact.

We close the second match, and then we close the index

and hit Enter.

We have an eye color of brown,

which matches the answer we got when we did it in two parts.

If we copy this down, we now have all of Rod-

picking up both the x and y variable.

We just built a two variable lookup in one single formula.

Now it's your turn to try in problem seven.

Use an INDEX MATCH MATCH to write a single formula that will pick up

not only the personal attribute, but also the person's name,

and find the matching information using the same data set and formula.

Good luck.

[MUSIC]

探索我们的目录

免费加入并获得个性化推荐、更新和优惠。

Coursera 致力于普及全世界最好的教育，它与全球一流大学和机构合作提供在线课程。

© 2019 Coursera Inc. 保留所有权利。

通过 App Store 下载

通过 Google Play 获取