1. Chandoo Advanced Excel

Chandoo Advanced Excel

What is VLOOKUP?Vlookup (V stands for 'Vertical') is an in-built function in excel which allows establishing a relationship between different columns of excel. VLOOKUP for Approximate Matches (TRUE Keyword as the last parameter)Consider a scenario where a table calculates discounts for the customers who do not want to buy exactly tens or hundreds of items.As shown below, certain Company has imposed discounts on the quantity of items ranging from 1 to 10,000:Now it is uncertain that the customer buys exactly hundreds or thousands of items. In this case, Discount will be applied as per the VLOOKUP's Approximate Matches.

In other words, we do not want to limit them for finding matches to just the values present in the column that are 1, 10, 100, 1000, 10000. Here are the steps:Step 1) Click on the cell where the VLOOKUP function needs to be applied i.e. Cell reference 'I2'.Step 2) Enter '=VLOOKUP' in the cell. NOTE: If you want to use TRUE as the last parameter, you can leave it blank and by default it chooses TRUE for Approximate Matches.

Vlookup function applied between 2 different sheets placed in the same workbookLet's see an instance similar to the above case scenario. We are provided with one workbook containing two different sheets.

Lookup from Right to LeftIn the above example, if it is given the Student ID, it would be so easy to find one’s corresponding Name and Grade by using vlookup. Mce dvd player activator software download 2017. However, if we would need to find out the Student ID from one’s name, vlookup fails to help 😦I hope my previous posts help you understand vlookup a bit more. You may be aware of a drawback of vlookup: the direction of the lookup. It ALWAYS lookups the value from the LEFTMOST column in the table, then return the corresponding value X column(s) to the right. Vlookup CANNOT return a value that is on the LEFT of the lookup value.(Yes, actually you may do left hand vlookup with CHOOSE function which many websites have described it. However, I find that too complicated for majority users.

Moreover, I prefer simplicity to difficulty! Index and Match is definitely a better option comparing to vlookup and Choose.)For this example, we know that Flora is the 6th value under Student Name. Her Student ID then the 6th (corresponding) value under Student ID. In other words, we need a formula to get the 6th value under Student ID. Here we come the INDEX function.INDEX FunctionsThere are actually two INDEX functions; one is array, the other one is reference.SyntaxINDEX(array,rownum,columnnum )INDEX(reference,rownum,columnnum,areanum )The more I learn about INDEX, the more I am fond of it.

🙂 Indeed, if you can master INDEX, you will be an Excel guru to most ordinary users.Nevertheless, for the sake of using INDEX together with Match as an alternative to vlookup, we can start with the basic of INDEX:INDEX(array,rownum,columnnum )Let’s confine the array in one row or column only.