MS Excel Logical Functions

Logical functions in Excel are powerful tools that help you make decisions based on conditions. Whether you're comparing values or testing multiple conditions, these functions save you time and effort. Let's explore the most common logical functions in Excel with easy-to-understand examples.

 

Logical Functions for Class Data Example

Student	Math	English	Science
Mohan	85	78	92
Sheela	45	63	55
Prem	76	88	81
Neha	95	82	90
Dinesh	58	49	65
Sneha	67	70	62
Mohini	88	92	79
Seema	34	59	46
Suresh	70	85	78
Priyanka	93	87	85

 

1. IF Function

We’ll use the IF function to assign grades based on total marks (the sum of Math, English, and Science).

Formula:
=IF(SUM(B2:D2)>=250, "A", IF(SUM(B2:D2)>=200, "B", IF(SUM(B2:D2)>=150, "C", "D")))

Explanation:

• If total marks are greater than or equal to 250, the student gets an "A".
• If between 200 and 249, they get a "B".
• If between 150 and 199, they get a "C".
• Otherwise, they get a "D".

For Mohan (who scored 85, 78, and 92), the total is 255, so the grade will be "A".

 

2. AND Function

Let’s check if students passed in all subjects (passing mark is 50).

Formula:
=AND(B2>=50, C2>=50, D2>=50)

Explanation:

• If Math, English, and Science marks are all 50 or above, the result is TRUE (student passed all subjects).
• Otherwise, it returns FALSE.

For Mohan, the result will be TRUE because he passed all subjects.

 

3. OR Function

The OR function will check if a student passed at least one subject.

Formula:
=OR(B2>=50, C2>=50, D2>=50)

Explanation:

• If any one of the three subjects has a score of 50 or more, the result is TRUE.
• Otherwise, it’s FALSE.

For Sheela, who has scores of 45, 63, and 55, the result will be TRUE because she passed in English and Science.

 

4. NOT Function

We’ll use the NOT function to flip the result of a condition. Let’s check if a student didn’t pass all subjects.

Formula:
=NOT(AND(B2>=50, C2>=50, D2>=50))

Explanation:

• If the student passed all subjects, the AND function will return TRUE.
• The NOT function will flip it to FALSE, meaning the student did not fail any subject.

For Dinesh, who failed in English (score 49), the NOT function will return TRUE, meaning he didn’t pass all subjects.

 

5. XOR Function

The XOR function (Exclusive OR) returns TRUE if one of the conditions is true, but not both. Let’s use it to check if a student passed exactly one subject out of Math and Science.

Formula:
=XOR(B2>=50, D2>=50)

Explanation:

• The function checks if the student passed exactly one subject (either Math or Science but not both).

For Sheela, who scored 45 in Math and 55 in Science, the result will be TRUE because she passed only one subject (Science).

 

6. IFERROR Function

The IFERROR function is used to handle potential errors in formulas. Let’s handle any error that might occur during a division operation (e.g., dividing total marks by 0).

Formula:
=IFERROR(SUM(B2:D2)/0, "Error")

Explanation:

• We are forcing an error by dividing the total marks by 0. Instead of showing an error message, IFERROR will return "Error".

For any student, this formula will return "Error" since division by 0 is not allowed.

 

7. IFNA Function

We’ll use IFNA to handle situations where a formula results in a #N/A error. This is useful when using functions like VLOOKUP or MATCH.

Formula:
=IFNA(VLOOKUP(A2, G2:H11, 2, FALSE), "Not Found")

Explanation:

• This checks if the student's name (in column A) is found in a list (in range G2

).

• If not found, instead of returning a #N/A error, the formula will return "Not Found".

If the student’s name isn’t found in the list, it will return "Not Found".

 

8. SWITCH Function

We can use the SWITCH function to assign grades based on total marks, just like we did with the IF function, but in a simpler and cleaner way.

Formula:
=SWITCH(TRUE, SUM(B2:D2)>=250, "A", SUM(B2:D2)>=200, "B", SUM(B2:D2)>=150, "C", "D")

Explanation:

• The function checks multiple conditions and returns the corresponding grade.
• If the total is 250 or above, the grade is "A". If between 200 and 249, it’s "B", and so on.

For Prem, with total marks of 245, the result will be "B".

 

Summary of Logical Function Applications

Student	Math	English	Science	Total Marks	Passed All (AND)	Passed Any (OR)	Didn’t Pass All (NOT)	Grade (SWITCH)
Mohan	85	78	92	255	TRUE	TRUE	FALSE	A
Sheela	45	63	55	163	FALSE	TRUE	TRUE	C
Prem	76	88	81	245	TRUE	TRUE	FALSE	B
Neha	95	82	90	267	TRUE	TRUE	FALSE	A
Dinesh	58	49	65	172	FALSE	TRUE	TRUE	C
Sneha	67	70	62	199	TRUE	TRUE	FALSE	C
Mohini	88	92	79	259	TRUE	TRUE	FALSE	A
Seema	34	59	46	139	FALSE	TRUE	TRUE	D
Suresh	70	85	78	233	TRUE	TRUE	FALSE	B
Priyanka	93	87	85	265	TRUE	TRUE	FALSE	A

 

Final Remarks

Logical functions in Excel such as IF, AND, OR, NOT, XOR, IFERROR, IFNA, and SWITCH allow you to create dynamic, decision-making spreadsheets. Using the data of a class of 10 students, we have seen how to implement these functions to calculate grades, check passing conditions, and handle errors.

 

How to Use the CHOOSE Function in Excel?

The CHOOSE function in Excel is a powerful tool that returns a value from a list based on a given position or index number. It can simplify complex decision-making processes, categorize data, or even dynamically assign values based on certain conditions.

In this blog, we will learn how to use the CHOOSE function by assigning names to employee IDs. We will explore different use cases and examples to give you a comprehensive understanding of the CHOOSE function.

 

What is the CHOOSE Function?

The CHOOSE function allows you to pick a value from a list of options based on an index number. Here’s the basic syntax:

Syntax:

=CHOOSE(index_num, value1, value2, value3, ...)

• index_num: This is the position or number that determines which value will be selected.
• value1, value2, value3, ...: These are the options (values) from which the function will select based on the index_num.

 

Example 1: Assigning Names Based on Employee IDs

Let’s take a simple example where you have employee IDs, and you want to assign names based on these IDs.

Employee ID	Name
1	Ajay
2	Ravi
3	Priya
4	Neha
5	Manish

 

  

Step 1: List of Employee Names

We’ll use the following names:

• Ajay
• Ravi
• Priya
• Neha
• Manish 

Step 2: Applying the CHOOSE Function

In cell B2 (assuming column A contains employee IDs), you can write the following formula:

 

=CHOOSE(A2, "Ajay", "Ravi", "Priya", "Neha", "Manish")

Explanation:

• If the value in cell A2 is 1, the formula will return "Ajay".
• If the value is 2, it will return "Ravi", and so on.

 

Step 3: Dragging the Formula

Once you’ve entered the formula in B2, drag it down to apply it to all rows. Here’s what the result looks like:

Employee ID	Name
1	Ajay
2	Ravi
3	Priya
4	Neha
5	Manish

 

 Example 2: Assigning Cities to Employees

Let’s take another practical example where we want to assign city to each employee based on their ID.

Cities List:

• Mumbai
• Delhi
• Bangalore
• Kolkata
• Hyderabad

 

Employee ID	City
1	Mumbai
2	Delhi
3	Bangalore
4	Kolkata
5	Hyderabad

 

Formula:

You can use the CHOOSE function to assign cities like this:

=CHOOSE(A2, "Mumbai", "Delhi", "Bangalore", "Kolkata", "Hyderabad")

 

Result:

Employee ID	Assigned City
1	Mumbai
2	Delhi
3	Bangalore
4	Kolkata
5	Hyderabad

This is a straightforward way to dynamically assign values from a list.

 

Example 3: Using CHOOSE with Numbers

The CHOOSE function is not limited to text values like names or cities; it can be used with numbers too.

Let’s say you have a list of employee IDs, and you want to assign salary levels (in lakhs) based on their IDs.

 

Employee ID	Salary (Lakhs)
1	6
2	8
3	5
4	7
5	9

Formula:

=CHOOSE(A2, 6, 8, 5, 7, 9)

 

Result:

Employee ID	Salary (Lakhs)
1	6
2	8
3	5
4	7
5	9

 The CHOOSE function automatically picks the salary based on the Employee ID.

 

Example 4: Combining CHOOSE with Other Excel Functions

You can combine the CHOOSE function with other Excel functions like MATCH, VLOOKUP, or IF to create more complex scenarios.

 

Using CHOOSE with MATCH for Dynamic Selection

Suppose we have a dynamic situation where we want to pick a name based on a condition. We’ll use MATCH to first find the position and then CHOOSE to return the correct name.

Here’s an example formula:

=CHOOSE(MATCH(A2, {1,2,3,4,5}, 0), "Ajay", "Ravi", "Priya", "Neha", "Manish")

Explanation:

• The MATCH function finds the position of the employee ID in the list {1,2,3,4,5}.
• CHOOSE then selects the corresponding name from the list.

This is useful when you have complex data tables and want to return values dynamically based on conditions.

 

Assigning Departments with CHOOSE

Let’s say you have a list of departments and want to assign employees to departments based on their ID.

Departments:

• HR
• Finance
• IT
• Marketing
• Operations

 

Employee ID	Department
1	HR
2	Finance
3	IT
4	Marketing
5	Operations

Formula:

=CHOOSE(A2, "HR", "Finance", "IT", "Marketing", "Operations")

 

Result:

Employee ID	Assigned Department
1	HR
2	Finance
3	IT
4	Marketing
5	Operations

This allows for easy allocation of employees to their respective departments.

 

Final Remarks

The CHOOSE function in Excel is an incredibly versatile tool that allows you to select values based on index numbers, making it ideal for scenarios like assigning names, cities, departments, or even numbers. In this blog, we’ve seen how to:

• Assign names to employees.
• Use CHOOSE to assign cities, salaries, and departments.
• Combine CHOOSE with other functions like MATCH.

With these examples, you can now easily use the CHOOSE function in your spreadsheets for various practical purposes.

 

How to use HLOOKUP in Excel?

The HLOOKUP (Horizontal Lookup) function in Excel is a powerful tool that allows you to search for a value in the top row of a table and return the value in the same column from a specified row. Unlike VLOOKUP (Vertical Lookup), which searches vertically down a column, HLOOKUP searches horizontally across the top row.

In this blog, we’ll explore the HLOOKUP function in detail, providing a practical example using some sales data.

 

Syntax of HLOOKUP

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

 

lookup_value: The value you want to search for in the first row of the table.

table_array: The table or range of data where the lookup will happen.

row_index_num: The row number in the table from which the value should be returned (the first row is 1).

range_lookup (optional): If TRUE (or omitted), an approximate match will be found; if FALSE, an exact match is required.

 

Here is some sales data of 5 Items for a year.

Sales Data

S.No	Product	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
1	Pen	120	110	140	130	150	160	145	155	135	125	140	150
2	Pencil	90	85	100	95	105	110	98	102	110	120	100	115
3	Eraser	75	80	90	85	95	85	100	92	80	85	95	105
4	Notebook	200	180	220	210	230	225	240	230	220	210	225	250
5	Sharpener	50	55	60	52	65	70	68	72	75	65	60	70

 

Scenario 1: Finding the Number of Notepad sold in Jul

1. Lookup Value: Jul.
2. Table Array: Our data is now in A1:N6.
3. Row Index Number: The Notepad is stored in the 5th row.
4. Range Lookup: Use FALSE for an exact match.

The formula would be:

=HLOOKUP("Jul", A1:N6, 5, FALSE)

This will return the number of Notepad sold in Jul Month.

 

S.No	Product	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
1	Pen	120	110	140	130	150	160	145	155	135	125	140	150
2	Pencil	90	85	100	95	105	110	98	102	110	120	100	115
3	Eraser	75	80	90	85	95	85	100	92	80	85	95	105
4	Notebook	200	180	220	210	230	225	240	230	220	210	225	250
5	Sharpener	50	55	60	52	65	70	68	72	75	65	60	70

 

 

 

Scenario 2: Finding the Number of Pen sold in Dec

1. Lookup Value: Dec.
2. Table Array: Our data is now in A1:N6.
3. Row Index Number: The Pen is stored in the 2nd row.
4. Range Lookup: Use FALSE for an exact match.

The formula would be:

=HLOOKUP("Dec", A1:N6, 2, FALSE)

This will return the number of Pen sold in Jul Month.

 

S.No	Product	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
1	Pen	120	110	140	130	150	160	145	155	135	125	140	150
2	Pencil	90	85	100	95	105	110	98	102	110	120	100	115
3	Eraser	75	80	90	85	95	85	100	92	80	85	95	105
4	Notebook	200	180	220	210	230	225	240	230	220	210	225	250
5	Sharpener	50	55	60	52	65	70	68	72	75	65	60	70

 

 

Final Remarks

The HLOOKUP function in Excel is a powerful tool for looking up data arranged horizontally, making it useful when your information is structured with row-based labels. While it may not be as frequently used as VLOOKUP, it is invaluable when working with horizontal data layouts, such as timelines, monthly reports, or data categories across rows.

Key takeaways for using HLOOKUP effectively:

1. Structure Matters: HLOOKUP works best when your data is organized in rows with the lookup value in the first row.
2. Flexibility: You can use HLOOKUP for a variety of tasks like looking up sales figures, employee records, or product details over time, where row labels are key, if such data is available.
3. Exact vs Approximate Match: Use FALSE for an exact match, which is generally more reliable for finding specific values. TRUE can be used for approximate matches when needed.

While HLOOKUP is ideal for row-based searches, it's essential to choose the right lookup function based on your data's structure. If your data is vertical (like in most cases), VLOOKUP or XLOOKUP might be more appropriate. Nonetheless, mastering HLOOKUP expands your ability to work efficiently with a wide range of data layouts in Excel.