Use vlookUp formula in MS Excel

Vlookup Excel

Objective

To apply the vlookup function in MS Excel.

When to use Vlookup

When you have a value in a cell in a worksheet (e.g. sheet1)  and you have a table in the same or another worksheet ( e.g. sheet1 or sheet2), then you can search the value in the left-most column of the above-mentioned table, and also refer the values corresponding to the matched value in the left-most column.

Approach

Let us try to understand it with the help of the image shown below. If you look at the image below you will see that we have a table which contains “City” in column “A” and “Count” in column “B”.

In column E we can see that in cell E2, we have the name of the city “Los Angeles”, and we want to find the “count” corresponding to the city “Los Angeles” in range A1 to A5.

So here in cell F2 we have applied the lookup formula. The vlookup formula as mentioned below it checks that value present in cell E2 is available in range A1:B5. Here the vlookup formula tries to check that text “Los Angeles” is found in range A1 to A5, it then finds that “Los Angeles” is found in cell A3, then it checks the corresponding “count” values in cell B3 , and the same result is shown in cell F2, which is 500.

 

Vlookup Excel

 

Here I have used the “$” sign in the formula, which basically fixes the formula range, so when you drag down the formula , range does not change.We have shown here only for one city here, but the same formula can be applied for multiple values as well. We just need to copy the formula down,