1. VLOOKUP: It is one of the most powerful lookup functions in Excel.
It is used to find a specific value from a range of values in a specific column. A Microsoft Excel formula is used to find specific information within a table or range of cells by row.
The VLOOKUP function has four arguments (or parameters).
- Search Value: This is the value to search for in the first column of the table array.
- Table Array: The range of cells containing the data to search.
- Col_index_number: This is the column number of the table array that contains the values you want to return.
- Range search: This is an optional argument that indicates whether close or exact matches are desired. If you omit this argument, Excel uses approximate matching.
For example, you have a data table containing employee information and you want to find the salary of an employee named John.
You can use the VLOOKUP formula to find the employee's name (lookup value) in the first column of the table array. The table array should contain columns for employee names and salaries. Salary is in the second column, so col_index_num should be 2. The range search argument is optional and can be omitted.
The resulting formula is:
= VLOOKUP("John", A1:B10, 2, FALSE)
This formula returns the salary of John from the table.
2: HLOOKUP: It is the horizontal LOOKUP.
It stands for "horizontal lookup." This is a Microsoft Excel function used to find a value in the first row of data and return the corresponding value in the same column as the target row.
Formula:
=HLOOKUP (search-value, table-array, row-index_number, [range-search])
- search value: The value to search for
- Table Array: The data table to retrieve the value from
- row_index_number: The row number in the table array for which the corresponding value will be returned.
- Optional range search: This is a logical value that indicates whether HLOOKUP should search for exact or approximate matches.
Example:
Suppose you have the following data table:
ID|Age
John|23|24
Maria|32|28
Use the lookup function to find John's ID.
The search value is "John."
The table array is the data table above.
row_index_number is
3. INDEX MATCH: It is a combination of two functions in Microsoft Excel. The INDEX function returns a value from a specific position within a range of cells, and the MATCH function finds a specific item within a range of cells and returns its relative position.
Example:
In cell A2 of the spreadsheet, you can use the INDEX MATCH formula to return the value of cell C5.
MATCH (A2, B1:B10, 0) INDEX (C1:C10)
In this example, the MATCH function finds the value in cell A2 in the range B1:B10.
returns the relative position of the value in the range if a match is found. In this case, the value of A2 is apple, so the MATCH function returns the value 5 (indicating that apple is the fifth element in the range B1:B10).
The INDEX function then uses the value returned by the MATCH function (5) to return the value from the fifth position in the range C1:C10, which is the value in cell C5.
4. OFFSET: The OFFSET function in Excel is a function used to return a reference to a range representing a specified number of rows and columns from a cell or range of cells. This is often used to create a dynamic range that stretches to fit the data.
For example, if you have a table of sales data and want to create a dynamic range that includes all the data in the table, you can use the OFFSET function. Start by selecting the first cell in the table, then use the OFFSET function to specify the number of rows and columns of cells to include in the range.
For example, if your table has 10 rows and 5 columns, you can create a dynamic range using the following formula:
= OFFSET (A1, 0, 0, 10, 5)
This includes cells A1-E10. Adding rows or columns to the table expands the range to include the new data.
You must be logged in to post a comment.