SOME ESSENTIAL EXCEL FORMULA SIMULAR
Excel Formula Simulator
Learn and practice essential Excel formulas with detailed explanations
What is VLOOKUP?
The VLOOKUP function searches for a value in the first column of a table and returns a value in the same row from a specified column. It's one of the most popular Excel functions for data lookup and retrieval.
When to use VLOOKUP?
- Looking up product information from an ID
- Finding employee details from an employee code
- Retrieving pricing information from a product list
- Any scenario where you need to find related information in a table
| Parameter | Description | Required |
|---|---|---|
| lookup_value | The value to search for in the first column of the table | Yes |
| table_array | The range of cells that contains the data | Yes |
| col_index_num | The column number in the table from which to retrieve the value | Yes |
| range_lookup | TRUE for approximate match, FALSE for exact match. Default is TRUE | No |
| Employee ID | Name | Department | Salary |
|---|---|---|---|
| 101 | John Smith | Marketing | $65,000 |
| 102 | Jane Doe | Sales | $72,000 |
| 103 | Robert Johnson | IT | $85,000 |
| 104 | Emily Davis | HR | $58,000 |
Example Usage:
To find the department of employee with ID 103:
=VLOOKUP(103, A2:D5, 3, FALSE) would return "IT"
To find the salary of employee with ID 102:
=VLOOKUP(102, A2:D5, 4, FALSE) would return "$72,000"
What is INDEX & MATCH?
The INDEX and MATCH functions combined provide a more flexible way to perform lookups compared to VLOOKUP. MATCH finds the position of a value in a range, and INDEX returns the value at a given position in a range.
Advantages over VLOOKUP:
- Can look to the left of the lookup column
- Doesn't require the lookup column to be the first column
- More efficient with large datasets
- More flexible when columns are added or removed
- Can perform horizontal and vertical lookups
| Function | Parameter | Description |
|---|---|---|
| MATCH | lookup_value | The value to search for in the lookup_range |
| lookup_range | The range of cells to search | |
| match_type | 1=less than, 0=exact match, -1=greater than | |
| INDEX | return_range | The range from which to return a value |
| row_num | The row position in the return_range | |
| column_num | The column position in the return_range (optional) |
| Product Code | Product Name | Category | Price |
|---|---|---|---|
| P1001 | Laptop | Electronics | $899 |
| P1002 | Desk Chair | Furniture | $249 |
| P1003 | Monitor | Electronics | $350 |
| P1004 | Notebook | Stationery | $12 |
Example Usage:
To find the price of a product named "Monitor":
=INDEX(D2:D5, MATCH("Monitor", B2:B5, 0)) would return "$350"
To find the product code for "Desk Chair":
=INDEX(A2:A5, MATCH("Desk Chair", B2:B5, 0)) would return "P1002"
What is DGET?
The DGET function extracts a single value from a column of a list or database that matches specified conditions. It's part of Excel's database functions and is useful when you need to extract specific information based on multiple criteria.
When to use DGET?
- Extracting a specific value from a database based on criteria
- When you need exact matches with multiple conditions
- Working with structured data in table format
- When VLOOKUP or INDEX/MATCH are not sufficient for complex criteria
| Parameter | Description | Required |
|---|---|---|
| database | The range of cells that makes up the database | Yes |
| field | The column from which to retrieve the value | Yes |
| criteria | The range of cells that contains the conditions | Yes |
| Product | Region | Salesperson | Sales |
|---|---|---|---|
| Laptop | North | John | $12,500 |
| Monitor | South | Sarah | $8,200 |
| Laptop | South | Mike | $10,800 |
| Monitor | North | John | $7,500 |
Example Usage:
To find sales of Laptops in the South region:
=DGET(A1:D5, "Sales", F1:G2) where F1:G2 contains criteria:
| Product | Region |
|---|---|
| Laptop | South |
This would return "$10,800"
What is HLOOKUP?
The HLOOKUP function searches for a value in the top row of a table and returns a value in the same column from a specified row. It's the horizontal equivalent of VLOOKUP.
When to use HLOOKUP?
- Looking up data in tables with horizontal orientation
- When your comparison values are located in a row rather than a column
- Working with time-series data organized by columns
- Extracting information from horizontally structured databases
| Parameter | Description | Required |
|---|---|---|
| lookup_value | The value to search for in the first row of the table | Yes |
| table_array | The range of cells that contains the data | Yes |
| row_index_num | The row number in the table from which to retrieve the value | Yes |
| range_lookup | TRUE for approximate match, FALSE for exact match. Default is TRUE | No |
| Quarter | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| Product A | $12,500 | $14,200 | $11,800 | $15,600 |
| Product B | $8,400 | $9,100 | $10,500 | $9,800 |
| Product C | $6,200 | $7,500 | $8,100 | $8,900 |
Example Usage:
To find Q3 sales for Product B:
=HLOOKUP("Q3", A1:E4, 3, FALSE) would return "$10,500"
To find Q1 sales for Product C:
=HLOOKUP("Q1", A1:E4, 4, FALSE) would return "$6,200"
What is FILTER?
The FILTER function filters a range of data based on criteria you define. It returns an array of values that meet your specified conditions, making it extremely useful for dynamic data extraction.
When to use FILTER?
- Extracting a subset of data that meets specific criteria
- Creating dynamic reports that update automatically
- When you need to return multiple matching records
- Replacing complex combinations of INDEX, SMALL, and IF functions
| Parameter | Description | Required |
|---|---|---|
| array | The range or array to filter | Yes |
| include | A Boolean array where TRUE includes the row/column | Yes |
| if_empty | The value to return if no results are found | No |
| Employee | Department | Salary | Experience |
|---|---|---|---|
| John | Sales | $65,000 | 5 |
| Sarah | Marketing | $58,000 | 3 |
| Mike | Sales | $72,000 | 7 |
| Emily | HR | $55,000 | 4 |
Example Usage:
To filter all employees in the Sales department:
=FILTER(A2:D5, B2:B5="Sales")
To filter employees with salary greater than $60,000:
=FILTER(A2:D5, C2:C5>60000)
What is the IF Function?
The IF function performs a logical test and returns one value if the test evaluates to TRUE, and another value if it evaluates to FALSE. It's one of the most fundamental and versatile functions in Excel.
When to use the IF Function?
- Creating conditional calculations
- Flagging data that meets specific criteria
- Building decision-making logic into spreadsheets
- Nesting with other functions for complex conditions
| Parameter | Description | Required |
|---|---|---|
| logical_test | Any value or expression that can be evaluated as TRUE or FALSE | Yes |
| value_if_true | The value to return if logical_test is TRUE | Yes |
| value_if_false | The value to return if logical_test is FALSE | Yes |
| Student | Score | Status |
|---|---|---|
| Alice | 85 | =IF(B2>=70, "Pass", "Fail") |
| Bob | 62 | =IF(B3>=70, "Pass", "Fail") |
| Carol | 92 | =IF(B4>=70, "Pass", "Fail") |
Example Usage:
To assign a pass/fail status based on a score of 70:
=IF(B2>=70, "Pass", "Fail")
To calculate a bonus of 10% for sales over $10,000:
=IF(B2>10000, B2*0.1, 0)
Shiben
As you came here, so you must go. So don't delay, do the main thing, many real genes will know or understand.
