Today I am Going to Disscuss The Newer Tips for Excel Users who wants to Be perfect in Excel.
So, Lets Start
1. VLOOKUP Function in Excel
Excel VLOOKUP function, a shortened form of Vertical Lookup, is used to locate specific information stored in a spreadsheet.
I.e. If you are looking to search for quantity and price of specific items in an inventory list, VLOOKUP can help you find data that matches specific criteria.
VLOOKUP searches for a value in the first column of a table_array and returns a value in the same row from another column in the table array. Refer to the image for more information on this example.
=VLOOKUP(F3,B3:D16,2,FALSE)
F3 - This VLOOKUP function is looking for the quantity in stock of Item No. F6546 – Cell Reference F3 (lookup_value)
B3:D16 - It is looking for this information in the data range, shaded Olive Green - Cells B3 to D16 (table_array)
2 - VLOOKUP is looking for the Quantity in the second column of the table (col_index_num)
FALSE - It indicates that only an exact match to the lookup _value F6546 will be accepted (range_lookup)
The VLOOKUP function search result : 58 in cell G3.
Excel VLOOKUP Function Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Lookup _value :
This is the value that is searched for in the first column of the table array. It can be a cell reference, text, number or a logical value.
Table_array :
This is the data range that the VLOOKUP function searches to find the specified information. Table_array must contain at least two columns of data. The first column contains the lookup_values and can be cell reference, text, number or logical value. Table_array is a named range or a reference to a range of cells.
Col_index_num :
This is the column number of the table_array from which the information is required. For example:
Col_index_num of 1 returns the value in the first column in table_array
Col_index_num of 2 returns the value in the second column in table_array, and so on.
Range_lookup :
A logical value (TRUE or FALSE) specified to find an Exact or an Approximate match to the lookup_value.
If TRUE or omitted, VLOOKUP will either return an exact or an approximate match to the lookup_value. If an exact match is not found, the next largest value less than lookup_value is returned.
Note : The values in the first column of table_array must be sorted in Ascending order, else VLOOKUP may give an incorrect value.
If FALSE, VLOOKUP will only use an exact match to the lookup_value. If there are two or more values in the first column of table_array matching the lookup_value, the first value found is used. If an exact match is not found, a #N/A error is returned.
So, Lets Start
1. VLOOKUP Function in Excel
Excel VLOOKUP function, a shortened form of Vertical Lookup, is used to locate specific information stored in a spreadsheet.
I.e. If you are looking to search for quantity and price of specific items in an inventory list, VLOOKUP can help you find data that matches specific criteria.
VLOOKUP searches for a value in the first column of a table_array and returns a value in the same row from another column in the table array. Refer to the image for more information on this example.
=VLOOKUP(F3,B3:D16,2,FALSE)
F3 - This VLOOKUP function is looking for the quantity in stock of Item No. F6546 – Cell Reference F3 (lookup_value)
B3:D16 - It is looking for this information in the data range, shaded Olive Green - Cells B3 to D16 (table_array)
2 - VLOOKUP is looking for the Quantity in the second column of the table (col_index_num)
FALSE - It indicates that only an exact match to the lookup _value F6546 will be accepted (range_lookup)
The VLOOKUP function search result : 58 in cell G3.
Excel VLOOKUP Function Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Lookup _value :
This is the value that is searched for in the first column of the table array. It can be a cell reference, text, number or a logical value.
Table_array :
This is the data range that the VLOOKUP function searches to find the specified information. Table_array must contain at least two columns of data. The first column contains the lookup_values and can be cell reference, text, number or logical value. Table_array is a named range or a reference to a range of cells.
Col_index_num :
This is the column number of the table_array from which the information is required. For example:
Col_index_num of 1 returns the value in the first column in table_array
Col_index_num of 2 returns the value in the second column in table_array, and so on.
Range_lookup :
A logical value (TRUE or FALSE) specified to find an Exact or an Approximate match to the lookup_value.
If TRUE or omitted, VLOOKUP will either return an exact or an approximate match to the lookup_value. If an exact match is not found, the next largest value less than lookup_value is returned.
Note : The values in the first column of table_array must be sorted in Ascending order, else VLOOKUP may give an incorrect value.
If FALSE, VLOOKUP will only use an exact match to the lookup_value. If there are two or more values in the first column of table_array matching the lookup_value, the first value found is used. If an exact match is not found, a #N/A error is returned.
Provide Us Feedback.
