The Excel\u00a0VLOOKUP<\/strong>\u00a0function has 4 arguments\u00a0Lookup_value,\u00a0<\/strong>Table_array, Col_index_num,\u00a0<\/strong>and\u00a0Range_lookup. \u00a0<\/strong>We will use an example based around staff salary calculations to explore each of these VLOOKUP<\/strong> arguments.<\/p>\n The\u00a0Lookup_value<\/strong>\u00a0is the value you want to look up in the Excel database or Excel list. In the example (figure 1) the\u00a0Lookup_value<\/strong>\u00a0is in\u00a0B3.\u00a0<\/strong>By entering\u00a0<\/strong>a staff id in\u00a0B3<\/strong>\u00a0the\u00a0VLookup\u00a0<\/strong>function can look up a value in the list with staff data. The\u00a0Table_array<\/strong>\u00a0is the data range and the value you want to look up must be in the first column of the\u00a0Table_array. Col_index_num<\/strong>\u00a0is the column number inside the\u00a0Table_array\u00a0<\/strong>you want the function to extract the data from and display it in the cell with the\u00a0Vlookup<\/strong>\u00a0function.<\/p>\n You have two options for the argument\u00a0Range_lookup.\u00a0<\/strong>If you enter\u00a0false<\/strong>\u00a0or\u00a00<\/strong>\u00a0(zero) the function will display\u00a0#N\/A\u00a0<\/strong>if it cannot find the\u00a0Lookup_value<\/strong>\u00a0in the Excel list or Excel database. It will only display a result if there is a perfect match between the\u00a0Lookup_value\u00a0<\/strong>and a value in the first column in the\u00a0Table_array.\u00a0<\/strong>If you enter\u00a0true<\/strong>\u00a0or\u00a01<\/strong>\u00a0the function will display a result if there is a perfect match\u00a0between the\u00a0Lookup_value\u00a0<\/strong>and a value in the first column in the\u00a0Table_array\u00a0<\/strong>and if there is no perfect match it will display the nearest lowest value. In other words if you in the example enter 12 in\u00a0B3<\/strong>\u00a0the function will return “Gwendy” because 10 is the nearest lowest value to 12. The first column in the\u00a0Table_array<\/b>\u00a0must be sorted in ascending order if you are using\u00a0True\u00a0<\/strong>or\u00a01\u00a0<\/strong>in\u00a0Range_lookup<\/strong>.<\/p>\n In the example\u00a0(figure 2) the\u00a0VLOOKUP\u00a0<\/strong>function is used to look up the raise based on current salary. The\u00a0Lookup_value<\/strong>\u00a0is the salary in\u00a0F3.\u00a0<\/strong>The\u00a0Table_array<\/strong>\u00a0is the range\u00a0$I$3:$J$10\u00a0<\/strong>(the lookup table). The range is absolute (the dollar signs) to be able to copy down the function without changing the range. The\u00a0Col_index_num\u00a0<\/strong>is\u00a02.\u00a0<\/strong>The raise percentage is in column 2 in the\u00a0Table_array<\/strong>. The\u00a0Range_lookup<\/strong>\u00a0is\u00a0true<\/strong>.\u00a0<\/strong>True\u00a0<\/strong>because you want the function to return a result also if there is no perfect match. If the salary the\u00a0Look_up\u00a0<\/strong>value is \u00a335,850.00 the function cannot find a perfect match in the\u00a0Table_array<\/strong>\u00a0but the nearest lowest value is 35,000 so the function will return 4%.<\/p>\n You can use the Excel\u00a0VLOOKUP\u00a0<\/strong>and\u00a0HLOOKUP<\/strong>\u00a0to compare two lists or Excel databases. The\u00a0Lookup_value<\/strong>\u00a0in this example (figure 3) is the staff id (it must be a unique value) in the first Excel list or database. The\u00a0Table_array<\/strong>\u00a0is the staff id range in the second list or database. Make the array absolute by using dollar signs around the cell references ($I$3:$I$9) again to be able to copy down the function without changing the range cell references. In the argument\u00a0Range_lookup<\/strong>\u00a0enter\u00a0false<\/strong>\u00a0because you only want a perfect match.<\/p>\n Copy down the function and if the function\u00a0displays\u00a0#N\/A<\/strong>\u00a0then it is because you do not have the record in the second Excel list or Excel database.<\/p>\n If you need to look up information in many columns you can refer to the columns using relative cell references in\u00a0Col_index_num<\/strong>. this will save you some time instead of entering the column number in each Excel\u00a0VLOOKUP\u00a0<\/strong>or\u00a0HLOOKUP\u00a0<\/strong>\u00a0function. In the example (figure 5) the column numbering is in row 4. To get the first name in\u00a0C3\u00a0<\/strong>the cell reference\u00a0C4\u00a0<\/strong>is entered in\u00a0Col_index_num<\/strong>. Copy the function across and the function will pickup the\u00a0Col_index_num<\/strong>\u00a0across from row 4.\u00a0Now it is very easy and less time consuming to add or remove columns from the array and also very easy to change the order of the information you want in row 3. You just need to change the numbering in row 4.<\/p>\n You can use nested\u00a0If\u00a0<\/strong>functions to make\u00a0Col_index_num\u00a0<\/strong>dynamic. In the example (figure 6) the bonus is based on the department and bonus category group. If the staff member works in the sales department and is in the bonus category group 1 the\u00a0VLOOKUP<\/strong>\u00a0function must return 3%, bonus category group 5 it must return 4%, and in bonus category group 10 5%. By nesting two\u00a0If<\/strong>\u00a0functions in\u00a0Col_index_num<\/strong>\u00a0you can use the information in column G the bonus category column to get the\u00a0VLOOKUP\u00a0<\/strong>function to return the right column from the\u00a0Table_array<\/strong>.\u00a0 Lookup_value\u00a0<\/strong>is the department in\u00a0E3<\/strong>. The\u00a0Table_array<\/strong>\u00a0is the range\u00a0$J$3:$M$7<\/strong>. Type\u00a0IF(G3=”Group 1″,2,IF(G3=”Group 5″,3,4))<\/strong>\u00a0in\u00a0Col_index_num.\u00a0<\/strong>In the first\u00a0If<\/strong>\u00a0function you want to find out if\u00a0G3<\/strong>\u00a0equals\u00a0“Group 1”<\/strong>. If it is true you want the\u00a0VLOOKUP\u00a0<\/strong>function to look up the bonus from column 2. If it is not true you want the second\u00a0If\u00a0<\/strong>function to find out if\u00a0G3\u00a0<\/strong>equals\u00a0“Group 5”<\/strong>.\u00a0\u00a0If it is true you want the\u00a0VLOOKUP<\/strong>\u00a0function to look up the bonus from column 3. If it is not true\u00a0you want the\u00a0VLOOKUP<\/strong>\u00a0function to look up the bonus from column 4.\u00a0In\u00a0Range_lookup<\/strong>\u00a0type false\u00a0because you only want to look up a perfect match.<\/p>\n In the example (figure 7) there are 3 tables. The data range in each table has a range name\u00a0Finance, Production, and Sales. In the example you want to lookup staff id 3 from the sales department. The range name is entered in\u00a0C19<\/strong>\u00a0and the staff id in\u00a0E19.\u00a0B22<\/strong>\u00a0is linked to\u00a0C19. Lookup_value<\/strong>\u00a0is\u00a0B22\u00a0<\/strong>(the staff id). The\u00a0Indirect\u00a0<\/strong>function is nested in\u00a0Table_array.\u00a0<\/strong>The\u00a0Indirect\u00a0<\/strong>function will see the content of\u00a0C19<\/strong>\u00a0as a range name.\u00a0Col_index_num<\/strong>\u00a0is the column number inside the\u00a0Table_array\u00a0<\/strong>you want\u00a0\u00a0the function to extract the data from and display it in the cell with the\u00a0VLOOKUP<\/strong>\u00a0function. In\u00a0Range_lookup<\/strong>\u00a0type false because you only want to lookup a perfect match.<\/p>\n The\u00a0Choose\u00a0<\/strong>function can also be useful to lookup data in two or more tables.\u00a0In the example (figure 8) the\u00a0VLOOKUP<\/strong>\u00a0<\/strong>function looks up the commission rate in the two commission tables to the right. The\u00a0Choose\u00a0<\/strong>function gets the table number from column\u00a0D<\/strong>.<\/p>\n In the example below (figure 9) a\u00a0VLOOKUP<\/strong>\u00a0<\/strong>function in C21<\/strong> is used to lookup the sales for a specific sales person (Richard) for as specific month (March). The\u00a0Match\u00a0<\/strong>function can find the position of a value or text string in a row or column and this information the\u00a0VLOOKUP<\/strong>\u00a0<\/strong>can use to get\u00a0the\u00a0Col_index_num<\/strong>. In the example the\u00a0Match\u00a0<\/strong>function return 4 to the\u00a0VLOOKUP<\/strong>.<\/strong><\/p>\n In the example (figure 10) a month needs to be\u00a0summarized. The\u00a0Sumproduct\u00a0<\/strong>function\u00a0can summarize an array. In C10\u00a0<\/strong>the month which needs to be summarized is entered.\u00a0The\u00a0HLOOKUP<\/strong>\u00a0is nested inside the Sumproduct\u00a0<\/strong>function. \u00a0Row_index_num<\/strong>\u00a0is in this example not only one row but five\u00a0rows (row 2 to 6). The curly brackets {2,3,4,5,6} tells Excel that it is not only one row but a number of rows (an array).<\/p>\n In the example (figure 11) 6 months for a specific sales team\u00a0needs to be\u00a0summarized. Again as the example above the<\/span>\u00a0Sumproduct\u00a0<\/strong>function\u00a0can summarize the\u00a0array. In\u00a0<\/span>C11\u00a0<\/strong>the name of the sales team is entered.\u00a0The Excel\u00a0VLOOKUP<\/strong><\/span>\u00a0is nested inside the\u00a0<\/span>Sumproduct\u00a0<\/strong>function. \u00a0Col<\/strong><\/span>_index_num<\/strong>\u00a0is many columns (column 2 to 7)<\/span>. Again the curly brackets {2,3,4,5,6,7} is used to tell\u00a0Excel that it is not only one columns\u00a0but a number of columns\u00a0(an array).<\/span><\/p>\n We have covered some detailed aspects of VLOOKUPs and how useful they can be when working with lists and databases. Other instances where you might use VLOOKUPS and HLOOKUPS<\/p>\n VLOOKUP in practice<\/a><\/p>\n How to use an Excel\u00a0VLOOKUP function in VBA<\/a><\/p>\n![\"lookup1\"](\"http:\/\/excelligent.files.wordpress.com\/2013\/04\/lookup14.jpg\")
<\/a>Lookup nearest lowest value<\/h3>\n
<\/a>Compare two lists using\u00a0VLOOKUP<\/strong><\/h3>\n
<\/a><\/a>Dynamic\u00a0Col_index_num\u00a0<\/strong>using numbered columns<\/h3>\n
<\/a>Dynamic\u00a0Col_index_num\u00a0<\/strong>using nested\u00a0If<\/strong>\u00a0functions\u00a0<\/strong><\/h3>\n
\n<\/strong><\/p>\n<\/a>Lookup data in more than one Excel list or Excel database using the\u00a0Indirect<\/strong>\u00a0function and range names<\/h3>\n
<\/a>Lookup data in more than one Excel list or Excel database using the\u00a0Choose function<\/h3>\n
<\/a>Lookup data in Excel list or database using the Match function to return the information from the right column in the array<\/h3>\n
<\/a>Lookup and\u00a0summarize\u00a0a column using the Sumproduct function<\/h3>\n
<\/a>Lookup and\u00a0summarize\u00a0a row using the Sumproduct function<\/h3>\n
<\/a>Summary – VLOOKUP function<\/h3>\n
\n
Resources<\/h2>\n