Blue Flower

VLOOKUP function

To browse the range of cells or database tables Excel contains several functions. I will here present some of these other functions on the search in Excel.

  • LOOKUP - (range, row)
    - Returns the results from the first right column in the same row where the condition or requested information, commonly used two columns side by side,
    =LOOKUP (lookup_value;array)
  • HLOOKUP - (row, row)
    - Horizontal search, uses the ranks of the search, the data in the header or the first row range of cells is a condition and returns the data of the number of rows that we set as an argument, the range of data can include the title row (Header)
    =HLOOKUP (lookup_value;table_array;row_index_num;range_lookup)
  • VLOOKUP (column, column)
    - Vertical search, use the first column of the search and returns the data from a column from the right side which finds condition. The requested information or condition is always in the first column, sintaksta for VLOOKUP is,
    =VLOOKUP (lookup_value;table_array;col_index_num;range_lookup)

Basics of Microsoft Excel VLOOKUP formula - Syntax VLOOKUP function

The letter 'V' in the title of Excel function VLOOKUP read as "vertically".

Syntax function

  • VLOOKUP(first_argument;the_second_argument;third_argument;fourth_argument)
  • VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)
  • VLOOKUP(required_data;range_data_in_one_we_are_looking_for;number_columns_of_that_return_result;identical_or_approximate_value)

VLOOKUP function we use when in the range of data we want to come back as a result of the information contained in the same row to the right of the conditions (lookup_value).

VLOOKUP compares the condition or criterion (lookup_value) and search it in the first column of the range of data and returns the results from the second, third, etc. column. If the VLOOKUP function finds more of the same data VLOOKUP returns the first in the order, so it is sometimes necessary to use the sorting if you use this function, or use other Excel function.

The Lookup_value

The argument the lookup_value the argument, the data that it search (a condition or criterion) in the first column of the range of data or tables. Lookup_value can be a value or reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns a #N/A.

The Table_array

The argument the table_array argument to VLOOKUP is a range of data or tables in which we are looking for the condition or criterion. This range of data can consist of two or more columns of data and the number of rows is irrelevant. The fact that we are looking for (a condition or criterion - lookup_value) always is in the first column of the range of data (table_array). The condition or criterion can be text, numeric/numeric or logical data. Do not differ case sensitive.

The Col_index_num

The argument the col_index_num the number of the column in the data range or table that you crawl (table_array) and from which it must return the value found. If for the col_index_num set number 1 then the VLOOKUP function returns the data or the value of the first column of the range of data (table_array), if for the col_index_num set number 2 then the VLOOKUP function returns the data or value from another column range of data (table_array) and so on.

If the argument col_index_num less than 1, VLOOKUP returns the #VALUE! error.

If the argument col_index_num greater than the number of columns in table_array, VLOOKUP returns a #REF! error.

The range_lookup

The argument is a logical value that specifies whether you want VLOOKUP finds identical or approximate value.

If the VLOOKUP Formula at the end you enter (for range_lookup):

  • TRUE => then the data in the first column must be sorted ascending (descending)
  • FALSE => then the data in the first column does not need to be sorted

If you enter TRUE or omit the fourth argument,

VLOOKUP formula returns identical or approximate value. If not found identical value, returns the next largest value that is less than lookup_value.

If you use TRUE as the fourth argument to VLOOKUP formula, values/data in the first column of table_array must be sorted in ascending order. Otherwise VLOOKUP formula may not give the correct value. To sort the values in ascending order using the sort in Excel

If you enter FALSE as the fourth argument,

VLOOKUP formula will find exactly identical value or data ie. A condition or criterion. In this case it is not necessary to sort the values in the first column of table_array. If in the first column of table_array there are two or more values corresponding to 'lookup_value', VLOOKUP formula returns as a result of the first detected value or of data. If not found identical value, it returns an error #N/A!

Absolute and Relative Address in the VLOOKUP formula

When you use the VLOOKUP formula/function, only in one cell and you have no intention to copy is enough to write the relative address for an argument the table_array. In case you want to copy created a formula that contains a VLOOKUP function then required for the second argument the table_array using Absolute Addresses range of data.

My advice is that you always naming cell ranges or range of data you are searching for. When naming a range of cells in Excel will be easier to create a formula with functions and especially with the VLOOKUP function/formula.

Learning to use the VLOOKUP function in Vlookup Formula

This simple formula (pictured below) that uses VLOOKUP function shows the basics of using the VLOOKUP function/formula. Note the range of data as well as rows and columns. Sequence number of the column is one of the arguments VLOOKUP function and search the range of data you use using VLOOKUP function/formula. I will show some examples of the different range of data because I want to understand (as a beginner) to use the number of the column as the third argument, and pay attention to the ranges of data or cells.

Example 1 - VLOOKUP function is used for search a range of data

In this example, the picture below you will see how to use arguments VLOOKUP function in Excel. On the picture you see that the range of data or cells that we search, column in which there is a requirement that we are looking for and column from which VLOOKUP formula should return the result from the same row or column that we have determined by entering number as the third argument. When we use the VLOOKUP function/formula  in the range of data we do not include header row, we use only data.

So, VLOOKUP Formula returns the result to the right side of the ordinal number of the column range of data that we have set. Instead of this number, we can use the COLUMN() function that can be nested within the VLOOKUP Formula but I will come to later.

Also, you notice on the picture that I created a formula with VLOOKUP function that contains a relative and absolute addresses and named range of cells database tables and columns ordinal numbers in the range of data.

Vlookup function in Excel VLOOKUP Formula 

 

Therefore, the Excel formula with a VLOOKUP function for this example can be in the following form.

=VLOOKUP(I2;B2:G9;3;FALSE)

=VLOOKUP(I2;$B$2:$G$9;3;FALSE) => required when you copy the formula to right

=VLOOKUP(I2;base;3;FALSE) => desirable/required when you copy the formula

=VLOOKUP($I$2;$B$2:$G$9;COLUMN(C1);FALSE) => Because the column 'C' is third (3)

=VLOOKUP($I$2;$B$2:$G$9;COLUMN(D1)-1;FALSE) => Because the column 'D' is fourth (4-1=3)

You notice the formula above with named range of cells ("base"). Also note that instead of the third argument, ie. the number that determines the number of the column range of data we can use the COLUMN() as I said at the beginning of the tutorial. COLUMN() Function is preferable to use when we need to copy the formula to the right and return the data from multiple columns

When you do not need to use the VLOOKUP function/formula

Excel VLOOKUP function use when we search ranges of data and we want to return data for a condition that is unique. Therefore, on the picture above might not use the VLOOKUP function when the set condition "mandarin" or "red" because it is not unique regardless of which would set the range of data that the first column is the one in in which there is data requested or condition. In this case, VLOOKUP function to us as a result of the shaft first record that finding because it can not determine that data we are looking for because there are more of the same data (conditions). 

Example 2 - VLOOKUP Formula

In this example, in the picture below I want to show you how to pay attention to the number of the column range of data that we searching. At the beginning of the tutorials I said that VLOOKUP function works with columns, therefore it is important to understand.

In the picture below you see a task that we need to solve. We need to search through the database (range of cells) that in this case the table range data 'C2:F5' and to set the condition the company 'C' to find a result that is in the 'Final' column which is the fourth column in a row.

VLOOKUP function in Excel formula - example 

The formula is as follows:

=VLOOKUP(H2;C2:F5;4;FALSE)
=VLOOKUP(H2;$C$2:$F$5;4;FALSE)
=VLOOKUP(H2;company;4;FALSE)
=VLOOKUP($H$2;$C$2:$F$5;COLUMN(D1);FALSE) => Because the column 'D' is fourth (4)
=VLOOKUP($H$2;$C$2:$F$5;COLUMN(F1)-2;FALSE) => Because the column 'F' is sixth (6-2=4)

Example 3 - VLOOKUP function searches data in the first column and returns the result of multiple columns

In the following example, you notice that the table range data is in a different range of cells. In this case use the VLOOKUP function in combination with the COLUMN function is advisable especially if you have to copy the formula to the right. In the picture below you will see that in same moment after the entry conditions. Excel automatically returns results that were set in columns K and L.

How to combine Excel functions VLOOKUP and COLUMN

 

Formulas are as follows:

- The formula in cell K2: =VLOOKUP($J$2;$E$2:$H$5;3;FALSE

=VLOOKUP($J$2;$E$2:$H$5;COLUMN()-8;FALSE)

=> We need to condition return the result from the third column range of data (3), since the column 'K' in which there is a formula eleventh in the order (11-8=3)

- The formula in cell L2: =VLOOKUP($J$2;$E$2:$H$5;4;FALSE)

=VLOOKUP($J$2;$E$2:$H$5;COLUMN()-8;FALSE)

=> We need to condition return the result in the fourth column range of data (4), since the column 'L' where there is a formula twelfth in the order (12-8=4)

- The formula in cell K4: =VLOOKUP($J$2;$E$2:$H$5;COLUMN(C1);FALSE

=> We need to condition return the result from the third column range of data (3), since the column 'C' third in the order (3)

- The formula in cell L4: =VLOOKUP($J$2;$E$2:$H$5;COLUMN(D1);FALSE)

=> We need to condition return the result in the fourth column range of data (4), since the column 'D' fourth order (4)