10 Questions
3 Views

# Excel Formulas: SUMIF and VLOOKUP

Created by
@Rakesh Chaubey

### What is the purpose of the SUMIF formula?

• To return a value based on a condition
• To sum values in a range based on a condition (correct)
• To look up a value in a table
• To format cells based on a rule
• ### What is the syntax for the VLOOKUP formula?

• VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) (correct)
• VLOOKUP(lookup_value, [range_lookup], table_array, col_index_num)
• VLOOKUP(lookup_value, table_array, [range_lookup], col_index_num)
• VLOOKUP(table_array, lookup_value, col_index_num, [range_lookup])
• ### What is the purpose of the INDEX-MATCH formula?

• To look up a value in a table
• To format cells based on a rule
• To sum values in a range based on a condition
• To return a value based on a lookup (correct)
• ### What type of rule is used in Conditional Formatting?

<p>All of the above</p> Signup and view all the answers

### What is the purpose of the IFERROR formula?

<p>To return a custom value if an error occurs</p> Signup and view all the answers

### What is the syntax for the INDEX-MATCH formula?

<p>INDEX(range, MATCH(lookup_value, lookup_array, [match_type]))</p> Signup and view all the answers

### What is an example of a rule used in Conditional Formatting?

<p>Format cells that are greater than a certain value</p> Signup and view all the answers

### What is the purpose of the VLOOKUP formula?

<p>To return a value based on a lookup</p> Signup and view all the answers

### What is the purpose of the SUMIF formula?

<p>To sum values in a range based on a condition</p> Signup and view all the answers

### What is the syntax for the VLOOKUP formula?

<p>VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])</p> Signup and view all the answers

## Study Notes

### Excel Sheet Formulas

#### SUMIF Formula

• Syntax: SUMIF(range, criteria, [sum_range])
• Returns the sum of cells in [sum_range] that meet the specified criteria in range
• Example: SUMIF(A1:A10, "&gt;10", B1:B10) returns the sum of values in column B for cells in column A that are greater than 10

#### VLOOKUP Formula

• Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Looks up the lookup_value in the first column of table_array and returns the value in the specified col_index_num column
• Example: VLOOKUP(A2, B:C, 2, FALSE) returns the value in column C for the value in cell A2 found in column B

#### INDEX-MATCH Formula

• Syntax: INDEX(range, MATCH(lookup_value, lookup_array, [match_type])
• Looks up the lookup_value in lookup_array and returns the value at the same position in range
• Example: INDEX(C:C, MATCH(A2, B:B, 0)) returns the value in column C for the value in cell A2 found in column B

#### Conditional Formatting

• Allows formatting cells based on specific conditions or rules
• Types of rules:
• Format cells that contain specific values or formulas
• Format cells that are greater than or less than a certain value
• Format cells that contain specific text or dates
• Example: Format cells in column A that are greater than 10 with a red fill color

#### IFERROR Formula

• Syntax: IFERROR(cell, value_if_error)
• Returns value_if_error if the cell contains an error, otherwise returns the value in the cell
• Example: IFERROR(A1/B1, "Divide by zero error") returns "Divide by zero error" if the division results in an error, otherwise returns the result of the division

### SUMIF Formula

• The SUMIF formula syntax is SUMIF(range, criteria, [sum_range])
• It returns the sum of cells in [sum_range] that meet the specified criteria in range
• The criteria can be a specific value, a formula, or a reference to another cell

### VLOOKUP Formula

• The VLOOKUP formula syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• It looks up the lookup_value in the first column of table_array and returns the value in the specified col_index_num column
• The range_lookup parameter can be set to TRUE or FALSE to specify an exact or approximate match

### INDEX-MATCH Formula

• The INDEX-MATCH formula syntax is INDEX(range, MATCH(lookup_value, lookup_array, [match_type])
• It looks up the lookup_value in lookup_array and returns the value at the same position in range
• The match_type parameter can be set to 1, 0, or -1 to specify an exact, exact or approximate, or exact match

### Conditional Formatting

• Conditional formatting allows formatting cells based on specific conditions or rules
• Types of rules include formatting cells that contain specific values or formulas, formatting cells that are greater than or less than a certain value, and formatting cells that contain specific text or dates
• Conditional formatting can be used to highlight important data, identify trends, and more

### IFERROR Formula

• The IFERROR formula syntax is IFERROR(cell, value_if_error)
• It returns value_if_error if the cell contains an error, otherwise returns the value in the cell
• The value_if_error parameter can be a specific value, a formula, or a reference to another cell

## Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

## Description

Learn about two essential Excel formulas: SUMIF and VLOOKUP. Understand their syntax, usage, and examples to improve your data analysis skills.

## More Quizzes Like This

Use Quizgecko on...
Browser
Information:
Success:
Error: