Practical No.

8: Excel Data Navigation &

Logical Functions
Aim: To data navigation and logical functions in Microsoft Excel, by use of VLOOKUP and
HLOOKUP for retrieving data from sales datasets, and applying COUNTIF, COUNTIFS, IF,
and IFS functions on employee datasets.

In the world of Excel, efficiently retrieving specific data from a large dataset is a crucial skill.
Two of the most fundamental and widely-used functions for this purpose are VLOOKUP and
HLOOKUP. These functions allow you to search for a value in a table and return a
corresponding value from a different column or row.

VLOOKUP (Vertical Lookup)

The VLOOKUP function is used to search for a value in the first column of a table and return a
value in the same row from a specified column. The "V" in VLOOKUP stands for vertical,
signifying that the function looks up data in a vertical or column-wise manner.

Syntax of VLOOKUP
The syntax for the VLOOKUP function is as follows:
Excel
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Let's break down each argument:

 lookup_value: This is the value you want to search for. It must be in the first column of
the table_array.
 table_array: This is the range of cells that contains the data. The lookup value must be in
the first column of this range.
 col_index_num: This is the column number within the table_array from which you
want to retrieve a value. The first column of the table_array is column 1, the second is
2, and so on.
 range_lookup: This is an optional logical value (TRUE or FALSE).
• TRUE (or omitted): This finds an approximate match. For this to work correctly,
the first column of your table_array must be sorted in ascending order. If an
exact match is not found, VLOOKUP will return the largest value that is less than
the lookup_value.

o FALSE: This finds an exact match. If an exact match is not found, the function
will return an N/A error.
VLOOKUP Example
Imagine you have a table of employee information:

To find the department of the employee with ID 103, you would use the following VLOOKUP
formula:

Excel
=VLOOKUP(103, A2:D5, 4, FALSE)

In this formula:

 103 is the lookup_value.

 A2:D5 is the table_array.
 4 is the col_index_num (the "Department" column).
 FALSE specifies an exact match.

This formula would return "IT".

HLOOKUP (Horizontal Lookup)

The HLOOKUP function is similar to VLOOKUP, but it searches for a value in the top row of
a table and returns a value in the same column from a specified row. The "H" in HLOOKUP
stands for horizontal, indicating a row-wise lookup.

Syntax of HLOOKUP
The syntax for the HLOOKUP function is:
Excel
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

The arguments are analogous to VLOOKUP:

 lookup_value: The value you want to search for in the first row of the table_array.
 table_array: The range of cells containing the data.
 row_index_num: The row number within the table_array from which to retrieve
a value. The first row is 1, the second is 2, and so on.
 range_lookup: An optional logical value (TRUE or FALSE) for an approximate or
exact match, respectively.
 HLOOKUP Example
Consider a table with sales data organized horizontally:
To find the sales of Product B in Q3, you would use this HLOOKUP formula:
Excel
=HLOOKUP("Q3", B1:E4, 3, FALSE)

In this formula:

 "Q3" is the lookup_value.

 B1:E4 is the table_array.
 3 is the row_index_num (the row for "Product B").
 FALSE indicates an exact match.
This formula would return $11,000.
. COUNTIF, COUNTIFS, IF, IFS on Employee Dataset

Next, create a sample Employee Dataset in a new sheet in the range A1:D6.

Sample Employee Data:

1. COUNTIF

COUNTIF counts the number of cells that meet a single condition.

Goal: Count the number of employees in the "Sales" department.
Steps:
1. Click on a blank cell (e.g., F2).
2. Type the formula: =COUNTIF(B2:B6, "Sales")
3. Press Enter.

Result: The cell F2 will show 2.

2. COUNTIFS
COUNTIFS counts the number of cells that meet multiple conditions.
Goal: Count the number of employees in the "IT" department whose performance is "Excellent".
Steps:
1. Click on a blank cell (e.g., F3).
2. Type the formula: =COUNTIFS(B2:B6, "IT", D2:D6, "Excellent")
3. Press Enter.
Result: The cell F3 will show 1.
3. IF

The IF function checks if a condition is met, and returns one value if TRUE, and another value if
FALSE.
Goal: Determine if employees are eligible for a bonus (Salary > 60000).
Steps:
1. Click on cell E2.
2. Type the formula: =IF(C2>60000, "Bonus", "No Bonus")
3. Press Enter.
4. Click on cell E2 again, grab the fill handle (the small square at the bottom-right corner),
and drag it down to E6 to apply the formula to all employees.
Result: Column E will show "No Bonus" for John and Jane, and "Bonus" for the rest.

4. IFS
The IFS function checks multiple conditions and returns a value corresponding to the first
true condition.
Goal: Assign a performance rating based on the Performance text.
Steps:
1. Click on cell F2.
2. Type the formula: =IFS(D2="Excellent", 5, D2="Good", 3)
3. Press Enter.
4. Drag the fill handle from F2 down to F6.
Result: Column F will show 5 for "Excellent" performance and 3 for "Good" performance.

c. Working with Multi-Cell Calculations (Array Formulas)

Array formulas perform calculations on a range of cells at once, rather than a single cell.

Goal: Calculate the total sales for "Quarter 1" by multiplying Price and Units Sold for a list
of products.
Sample Data:
Create this new table in the range A1:C4.

Product Price Units Sold

1. Click on a blank cell where you want the total sales (e.g., D2).
2. Type the following formula: =SUM(B2:B4*C2:C4)
3. Instead of pressing Enter, press Ctrl + Shift + Enter.
 What Happens:
 Excel automatically places curly braces { } around your formula, indicating it's an
array formula: {=SUM(B2:B4*C2:C4)}.
 The formula multiplies the Price by the Units Sold for each row (100*50, 150*30,
200*25).
 The SUM function then adds up the results of these multiplications (5000 + 4500 +
5000).

Result: The cell D2 will display the total sales value: 14500.

Conclusion:
The practical helped in understanding how to efficiently retrieve and analyze data using
Excel functions. VLOOKUP and HLOOKUP simplified searching for specific values in
large datasets, while logical functions like IF, IFS, COUNTIF, and COUNTIFS enabled
conditional analysis of employee data. Multi-cell calculations improved accuracy and
productivity in handling bulk data. Overall, these functions are essential tools for data
management, analysis, and automation in Excel.