How to use VLOOKUP with self contained table in Excel

In this article, we will learn How to use VLOOKUP with a self contained table in Excel.

Scenario:

Sometimes, when working with datasets. We need to find the value based on some other value. For example finding the grades of students based on scores of an exam or finding the points scored based on the result of the match. For this, we generally use nested IF formula or using the VLOOKUP function with a self contained table. Yeahh..That's right VLOOKUP function. Below is the explanation to how to use VLOOKUP function with a self contained table.

How to solve the problem?

For this we will be using VLOOKUP function. VLOOKUP function takes 4 arguments which are lookup value, lookup table, column index and match_type. Lookup array argument can be given within curly braces and semicolons. Below is the formula to explain you better.

Generic formula:

=VLOOKUP(value, { r1c1 , r1c2 ; r2c1 , r2c2 ; r3c1 , r3c2,.. },col_index,0)

table given with curly braces, rows separated using the semicolon (;) and columns separated using the commas (,)

value : lookup value

r1c1 : first row and first column table value

r1c2 : first row and second column table value

r2c1 : second row and first column and then goes on

col_index : the lookup column index

0 : match_type

 

Example :

All of these might be confusing to understand. Let's try to understand via using it with an example. Here we have a UEFA game records of the matches played. We need to find the points gained by each team. Generally we make a points table and fetch that table as an argument. But in this we can fetch table with formula as shown below.

Use the formula :

= VLOOKUP ( D3 , { "Win",3 ; "Lose",-1 ; "Draw",1 } , 2 , 0)

Explanation : 

  1. Here VLOOKUP function lookup value in column 1.
  2. Then returns a value corresponding to the matched lookup value in column 2. 

As you can see in the above image that the points gained by the team on Draw match is 1. We calculated that using a VLOOKUP formula using a self contained table. Now copy the formula to other cells as shown below.

As you can see,we obtained all result values using a single formula. As we discussed earlier we can also perform the same task using the nested IF formula explained below.

 

How to use nested IF formula for the same table :

Use the IF function multiple times as shown below for the formula. Learn more about nested IF formula here.

Use the formula :

= IF ( D6 ="Win" , 3 , IF ( D6 = "Lose" , -1 , IF ( D6 = "Draw" , 1 , "" ) ) )

As you can see in the above image, all the points matching result values are here. The reason is why we do not generally use nested IF because more bigger the table more we will be using the formula and another reason is in the VLOOKUP formula we can add multiple rows, so we can extract multiple values using the same formula, but this doesn't happen in nested IF case. You can also find out the marks or score of the test using the same formula. Learn more about extracting values from the table here.

Here are all the observational notes regarding using the formula. 

Notes :

  1. You can add more rows and columns in the lookup array.
  2. Text arguments must be given within quotes ("").
  3. The VLOOKUP table must have the lookup_array in the leftmost or the first column. 
  4. The col index cannot be 1 as it is the lookup array
  5. 0 argument is used for the exact match value. Use 1 for the approximate match value.
  6. The function returns #N/A error, if look up value is not found in the lookup array. So catch the error, if necessary.

Hope this article about How to use VLOOKUP with a self contained table in Excel is explanatory. Find more articles on look up  formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

Related Articles

How to Retrieve Latest Price in Excel : It is common to update prices in any business and using the latest prices for any purchase or sales is must. To retrieve the latest price from a list in Excel we use the LOOKUP function. The LOOKUP function fetches the latest price.

VLOOKUP function to calculate grade in Excel : To calculate grades IF and IFS are not the only functions that you can use. The VLOOKUP is more efficient and dynamic for such conditional calculations.To calculate grades using VLOOKUP we can use this formula.

17 Things About Excel VLOOKUP : VLOOKUP is most commonly used for retrieving matched values but VLOOKUP can do a lot more than this. Here are 17 things about VLOOKUP that you should know to use effectively.

LOOKUP the First Text from a List in Excel : The VLOOKUP function works fine with wildcard characters. We can use this to extract the first text value from a given list in excel. Here is the generic formula.

LOOKUP date with last value in list : To retrieve the date that contains the last value we use the LOOKUP function. This function checks for the cell that contains the last value in a vector and then uses that reference to return the date.

Popular Articles:

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube