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, you can visit the following link
- 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 functions - Syntax VLOOKUP function
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 function VLOOKUP 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 function VLOOKUP 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 function VLOOKUP 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!
If the argument col_index_num greater than the number of columns in table_array, VLOOKUP returns a #REF!
The range_lookup
The argument is a logical value that specifies whether you want VLOOKUP finds identical or approximate value.
If the formula Vlookup 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 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, values/data in the first column of table_array must be sorted in ascending order. Otherwise, VLOOKUP 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 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 returns as a result of the first detected value or of data. If not found identical value, it returns an error #N/A!
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 function VLOOKUP 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 function VLOOKUP 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 function VLOOKUP 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!
If the argument col_index_num greater than the number of columns in table_array, VLOOKUP returns a #REF!
The range_lookup
The argument is a logical value that specifies whether you want VLOOKUP finds identical or approximate value.
If the formula Vlookup 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 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, values/data in the first column of table_array must be sorted in ascending order. Otherwise, VLOOKUP 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 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 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 function VLOOKUP
When you use the VLOOKUP 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 function VLOOKUP 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.
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.
Learning to use the VLOOKUP function
This simple formula (pictured below) that uses VLOOKUP function shows the basics of using the VLOOKUP function. Note the range of data as well as rows and columns. The sequence number of the column is one of the arguments VLOOKUP function and search the range of data you use using VLOOKUP function. 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 searching 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, the column in which there is a requirement that we are looking for and column from which VLOOKUP should return the result from the same row or column that we have determined by entering the number as the third argument. When we use the VLOOKUP function in the range of data we do not include the header row, we use only data.
So, VLOOKUP function 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 function COLUMN() that can be nested within the VLOOKUP function but I will come to later.
Also, you notice on the picture that I created a formula with VLOOKUP function that contains a relative, absolute address and named range of cells database tables and columns ordinal numbers in the range of data.
So, VLOOKUP function 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 function COLUMN() that can be nested within the VLOOKUP function but I will come to later.
Also, you notice on the picture that I created a formula with VLOOKUP function that contains a relative, absolute address and named range of cells database tables and columns ordinal numbers in the range of data.
Therefore, the formula with a function VLOOKUP 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. Function COLUMN() is preferable to use when we need to copy the formula to the right and return the data from multiple columns
=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. Function COLUMN() 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
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 functions 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 function
In this example, the picture below I want to show you how to pay attention to the number of the column range of data that we search. 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.
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.
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)
=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 function COLUMN 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.
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)
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)