Objectives. By the end you should be able to use lookup and reference function, choose match and index, combine the match and index function for flexible output. Now, we will see how to use some of the lookup and reference functions in Excel, we will see how to use choose index and match and also how to use index and match together. Let us start with the use of choose. Now, suppose I have a sheet where there are certain number of candidates who have appeared for interview. These candidates have given the written test and they interview their scores have been put here in the sheet and I have arranged the data in the descending order of their total.

I have a requirement that whenever I write a rank in this field see rank the results cells should show me which particular person has the fourth rank. So, in this case, the result cell should show me Krishna over here. Now, in order to get this output, we can use choose function in Excel, the format of choose function is like this equal to choose index number. Now, what is this index number? It is the number of output that we are looking to get. In this case, we will write four comma all the values in the order of their ranks.

So we'll write coats Sushil Kumar, Kavita coma and so on and so forth in this way. Now we close this bracket and hit on Enter, it will give us the value Krishna. So what we have done here is we have written choose four and in quotes, we have written all the names As per the order this is one way of writing this formula, another way is equal to choose by giving references for example, choose rank column, because we want to change the result as per the rank that we have input comma, select all these candidates one by one. Now, we will select this first cell, B two comma D three comma before and so on and so forth, close the bracket and enter with this we get the same result and it also has a benefit that whenever we change the rank the output changes. Hence, this the better way of writing the function.

Now, let us see how to use the match function. MATCH function searches for specified value in a range of cells and returns the range relative position of that cell. For example, from this list, I want to find who has got a written test score of 16. For that I have created these cells over here, a written test score of 16, I want to find the result that is the name of the candidate who has got written test score 16. For that, I would write equal to match lookup value that is 16. Instead of giving reference here, I could have also written 16, as in the given case, but that would make this formula static.

Hence, I would select this cell so that whenever I change the cell value, I would get a different result. I would write comma, all these cells, comma. Now this is a match type. It's one, zero or minus one. For the time being, we will write zero because we want an exact match close bracket and Enter. And here we can see the candidate with rank three has got a written test score 16.

Now, while writing this formula, we saw that the last cell can be either one zero or minus one, let us understand this minus one is four greater than, as in whenever I write written test score is 16. And my data is organized in descending order, just the way we have done, we would get the result that is matching the greater than type. For example, here it is showing rank three is matching 16. What if I changed it to 17, it will give us rank two. What Excel has done is it's returning any value that is greater than 17 in this lookup range, and has returned the position of the value that is matching 17 or greater than 17. If I would have returned The value of 17 and last match type as zero, the exact match, the formula would have given us an error like this.

Similarly, we could use match type as less than only when our data is organized in an ascending order. We had seen how to use a choose function on one column, what if I want to extend this facility of one column and extend it to my entire table? For that, I would need to use an index function. Let us see how to use it. I want a total of the candidates who have got ranked six in this case. So Mitra has got rank six.

So we should get an output. So Mitra in this particular cell, let us see how to do it using an index function. Let us write equal to index and Addy it has to be of writing it will consider the first one, the ad a format will select all the cells from which we can get an output. In our case it is b 228, comma, the row number. Now the row number in our case is given by the rank. So I will select this cell, comma, the column number.

Now, our table has 12344 columns, and we want an output of the total. Hence, we will select the fourth column, close bracket, and enter. So here we know that the rank six candidate got a total score of 21. We could also change this formula to represent a written test score by changing this column number two to an enter. So now we know that we can get any output from this table. If we use an index function.

We have seen how to use the match function And how to use index function. Now we will see how to use these functions together to get an output, which is much more flexible than the V lookup function, moreover, takes less time than the V lookup function. In order to use index and MATCH function together, let us create a scenario. What I want from this entire table is whenever I write the name of the candidate, I should get the scores of the candidate or if I want, I should get the rank of the candidate. For this, let us write equal to index. Now I'll select the entire table, comma, for the row number, I would use the match function, match the name with the column where the name occurs in this table, comma, exact match.

So I will put a zero close bracket comma and the column number. Now for the column number, we can use any value from one to five If I want rank, I could write one. If I want the total scores, I would write five. For the time being, let us write five, close bracket, and enter. So now we get an output as 29. Because the total score of Raja in this table is 29.

If I change the name to something else, like Sunita, the total score of Sunita is seen in this output cell. Now as I mentioned earlier, if I change the column number to something else, I would get a different output. Let's say I don't know the rank of this particular candidate, and I want the rank of the same. So I would write column number one at the end of this function and hit on Enter. Now I know Sunita has got rank five in the table. Now this particular case of searching for the rank with the name of the candidates would not have been possible by using v lookup function because the Blank column appears on the left hand side of the candidates column.

Hence, index and match can be very useful in such cases. We can also use index and match as an added formula and give results of a nested v lookup. We will see it in some other exercise. Summary you have learned, use lookup and reference function, choose match and index combine the match and index function for flexible output.