7] =VLOOKUP
=VLOOKUP is a function used to look up values in a table or range of cells based on specific criteria, and return the corresponding values from the specified column. This function belongs to the Search and Reference category, and is one of the most useful functions for combining data from different sources or for searching for specific information in a table.
The syntax of the =VLOOKUP function is as follows:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])Where:
- lookup_value is the value that you want to look up in a table or range of cells. This value can be a number, text, date, time, or cell reference.
- table_array is the table or range of cells that contains the data you want to search. This table or range of cells must have at least two columns, and the value you want to look up must be in the first column.
- col_index_num is the column number in the table or range of cells that contains the value that you want to return. This column number must be a positive number, starting at 1 for the first column, 2 for the second column, and so on.
- [range_lookup] is an optional argument that specifies whether you want to look up an exact value or one that is approximate. This value can be TRUE or FALSE, or 1 or 0. If you enter TRUE or 1, or leave these arguments blank, the =VLOOKUP function looks for values that come close to, that is, values less than or equal to lookup_value, assuming that the first column in the table or range of cells is already sorted ascendingly. If you enter FALSE or 0, then the =VLOOKUP function will look up the exact value, that is, the exact same value as lookup_value, regardless of the order of the first column in the table or range of cells.
Here is an example of using the =VLOOKUP function:
| A | B | C | D |
| Name | Value | Information | Code |
| Andi | 80 | Pass | A001 |
| Mind | 70 | Pass | A002 |
| Cici | 60 | Pass | A003 |
| Dedi | 50 | Fail | A004 |
| One | 40 | Fail | A005 |
- =VLOOKUP(“Mind “,A1:D5,2,FALSE) returns the value 70, which is the value returned from the second column in the table or cell range A1:D5, based on the “Bob”value searched in the first column, by using the exact value lookup.
- =VLOOKUP(“Cici”,A1:D5,3,TRUE) returns the text“Pass”, which is the value returned from the third column in the table or cell range A1:D5, based on the “Cici”value searched in the first column, by using an approximate value lookup.
- =VLOOKUP(A2,A1:D5,4,FALSE) returns the text“A001″, which is the value returned from the fourth column in the table or cell range A1:D5, based on the values in cell A2 that were searched in the first column, by using the exact value lookup.
- =VLOOKUP(45,A1:D5,1,TRUE) returns the text “Dedi”, which is the value returned from the first column in the table or cell range A1:D5, based on the value 45 searched in the first column, by using an approximate value lookup.