I have learnt the most useful function from this course - VLOOKUP. Let me summarise my learnings here. Also I have attached a working document. VLOOKUP is one of the most useful and important functions in Microsoft Excel. It is generally used to look up a particular value in huge data sheets where manual intervention can be cumbersome.
The alphabet ‘V’ in VLOOKUP stands for “Vertical” so this function is sometimes also called vertical lookup. The term “Vertical” signifies that it can be used to look up values vertically i.e. it can be used to look up values inside a column. Its syntax is as follows: =VLOOKUP( lookup_value, table_array, column_index, range_lookup )
Here, ‘lookup_value’ specifies the value to be searched inside the ‘table_array’. It can either be a value or a reference. ‘table_array’ is the range with two or more columns. ‘table_array’ argument can receive a range reference or a named range. The leftmost column of this range must contain the ‘lookup_value’. ‘column_index’ is the relative index of the column whose value needs to be returned by the VLOOKUP function. A ‘column_index’ 1 would return values from the first column in the ‘table_array’.
Similarly ‘column_index’ 2 would return values from the second column in the ‘table_array’. ‘range_lookup’ is a Boolean value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If its value is ‘True’ then either an approximate or an exact match will be returned. Here, if an exact match is not found, the next value that is less than ‘lookup_value’ is returned. If its value is ‘False’ then only exact match will be returned. Few Important points about Vertical Lookup function: VLOOKUP function performs a case-insensitive lookup. Excel VLOOKUP returns a ‘#N/A’ if it is not able to find the ‘lookup_value’ inside the ‘table_array’. It returns a ‘#VALUE!’ error if the value of ‘column_index’ is less than 1.
It returns a ‘#REF!’ error if the value of ‘column_index’ is greater than the number of columns in the ‘table_array’. The default value of ‘range_lookup’ is TRUE. So, it is better to omit this argument in case you need to perform either an approximate or an exact match. Vertical Lookup allows you to use wildcard characters in the ‘lookup_value’ argument.
Published : Jun 6th 2018
Master Lookup Functions in Excel for Accounting and…