Lookup a value in a left column of a table or an array.
The Excel VLOOKUP function extract data from a given column of a table or an array of values. So, if a given data is laid in a vertical pattern which means heading in 1st column and value begins from the 2nd column, and we need to look up and retrieve data from left to right for the value, in such cases we use VLOOKUP. The VLOOKUP function is a ‘vertical (V)’ lookup.
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
lookup_value – The value to lookup in the first column of the table
table_array – The table to extract lookup_value
col_index_num – The row number to return the corresponding value
range_lookup [optional] – A Boolean argument. TRUE (Default) – to lookup a partial match and FALSE for an exact match. The function returns #N/A error value if VLOOKUP cannot find any value as per arguments (Exact or Partial).
The corresponding value of a given column number
- The VLOOKUP function returns the value by going right to the specified column (vertical) and returns the value in the formula cell.
- The VLOOKUP function is not case sensitive.
- The VLOOKUP function works just like HLOOKUP function except that value lookup is across a specified number of rows (horizontal).
- In case [range_lookup] argument is TRUE (partial match) the function matches the nearest value, which may not be the expected result. It is advisable to select FALSE if you require an exact match of given criteria.
- The first column of the lookup table (table_array) needs to be sorted in ascending order if [range_lookup] is TRUE. Otherwise, VLOOKUP may return an incorrect value.
- If the lookup_value argument is text (and the [range_lookup] is FALSE), the user can use the wildcard characters asterisk (*) and question mark (?).
- A question mark matches a single character and an asterisk matches multiple characters. A criteria a*a will match with all cells containing a string beginning with “a” and ending in “a” like a text “abide” or “abate”.
Example (Image above)
When lookup value in column and return value of another row like here as table is vertical form and user want cost of sales in quarter 1st for this we use formula
=VLOOKUP(C11,$B$4:$D$8,3,FALSE) it return 170.