The VLOOKUP is the quickest, most convenient and simplest Excel formula for finding information from a list. So, providing you store your data vertically in a list, the VLOOKUP will allow you to identify a particular item then discover associated information about it.
The VLOOKUP function is able to use a given reference and look for it in a specified range and then return a value from the same row in another column. You can instruct the function to find an exact match or the closest match. The former is best used for ID’s when you need to find a person’s employee number or a product ID for example, the latter is useful for finding a graded result if the lookup criteria sits in between a bracketed range.
The function broken down looks like the below, in summary we will ask:
=VLOOKUP(Your reference, The range where your reference and result appears, which column number in the range is the result found in, do you want the reference to match exactly or a close match?)
In simple terms:
Find my reference in a range, when you find it, please tell me the piece of information that is x amount of columns to the right of that data
Let’s take a look at the structure of the VLOOKUP as found in Excel.
=VLOOKUP(lookup_value, table_array, col_index_number, [range index number])
Breaking down each element, Excel defines these as:
Is the value to be found in the first column of the table, and can be a value, a reference, or a text string. This is your employee or product ID I mentioned above
Is at table of text, numbers, or logical values, in which data is retrieved. Table_array can be a reference to a range or a range name.
Is the column number in a table_array from which the matching value should be returned. The first column values in the table in column 1.
[range index number]
Is a logical value: to find the closet match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE. Additional note, you can also use a zero 0 (for FALSE) or a one 1 (for TRUE).