All language subtitles for 5. Lookup and Reference Formulas Case Study - Solution

In this video we will use look on a reference formulas to help become fine.

The top three selling products for 2015 and 2016 fans will find the best selling product of 2015 the

number one selling product they will use though we look up formula to find this product.

The relook at formula set is to a set of data on table starting from the leftmost column you provide

a number or a value that we look up should find in the leftmost column of the dataset and when it finds

that value it will travel to the right and find the value that you want return.

In this case we want we look up formula to find the number one selling product in 2015.

So we will pass one on the first to sell a 15 as the lookup of value to relook at formula and will pass

this data set which has the same thing.

And the product name as a data table where we look up should search.

We look up will first look to the column in the first column and will take to find number one it will

find it in the sixth row then it will parallel to the right to that column that we want it to go to

and return the value at the intersection of the column and the row of it has this number one so we will

pass it.

Column number two they didn't go to column two and row six and then leave this point I will get this

as the card listing GV the product name in the cell so let us know that.

Let us enter the formula

here is the formula that we are using to find the number one selling product of 2015.

We look up is a formula function name the cell 15 which has the lookup value.

Number one selling A2 to be eleven has the sales rank and product name.

It has two columns still column in column B and 10 rows 8.

So we look up will take this lookup value from say a 15 which is 1 and look in the first or the leftmost

column of the data in that we are passing so look to this column for value 1 then we are asking you

to return the value that it finds in column 2 for the corresponding sell.

So once it finds one in column may it should travel to the second column in the same row and then the

value from that.

Finally we are passing false as the range look up which means that we are looking for exact match by

default.

We look at looks for an approximate match for the lookup value if you wanted to look for an exact match

for lookup value you need to pass.

Then you look at parameter as thoughts are 0.

Let us execute this formula and you can see that it return as Descartes sixteen DV which is the number

one selling product of 2015 we can now copy this formula to sell be 16 and be 17 to get the second ranking

and third ranking product.

So now we have top tier products for 2015 listed in this table let's now find the top 10 products for

2016 to select sell be 15.

This is very well into the formula to find the number one selling product of 2016.

The sales ranking for 2016 is in this cell range as to where for 11 days a slight problem though we

cannot use.

We look up function in this case because the sales rank is on the right hand side of that data table

the name is on the left the sales tank or the lookup value is on the right that we look up formula always

travels from the left to the right.

So what do we do in this case.

We'll use a different function to find the top selling product we'll use a combination of mat and index

function to get this information to select similarly 15 and let us enter the formula like this that

we have entered the formula that we want to use.

Let us see how this form levels it has two functions.

The index function and that match function.

In this formula first the max function will be executed.

You give it a lookup value and a cell range in which to look out for that value and return the raw number

of column number that corresponds to the cell with the value that you're giving in that lookup value

in this case.

We are passing lookup value as select 15 which is 1 and the lookup range as if to tell everyone where

we have the sales rank for 2016 friendly.

We are passing my type as 0 which means we are looking for an exact match.

If you don't give zero match you look for an approximate match.

So the way the match formula will work is it will start by looking in cell 15 because the value of 1

and then it'll go and search the string F2 to have 11 and find a cell which adds value 1 and it will

find this third cell in this range or third row in this range which has the value on the return 3.

Then the index function will be executed the index function takes an array or a range of cells as input

and returns the value from a cell that matches the raw number or column number that you specify in this

case Matt will return the raw number as 3 as we already discussed.

The index function will look in this settlement B2 to be eleven which has a product name and will go

in row 3 which has its DC mobile phone and it'll return as DC mobile phone let's execute this formula

and you can see it on SPC mobile phone.

In this way we use a combination of index and match to replicate the functionality of V look up the

combination of index and match function can overcome the limitations of V lookup so it can do everything

that we look up can do and more we usually look up because it's easier to use.

This is just one formula but in cases where we cannot use it we'll use the index and the match combination

to solve our problem we can now copy formula from 15 to 16 and 17 to find the second and third ranking

products of 2016.