Page 450 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

2 Create a dataset with columns EmployeeID, Name, Department, and Salary. Use VLOOKUP to find the salary
of an employee with a given EmployeeID.
3 Create a dynamic table that allows users to input an EmployeeID, and VLOOKUP should automatically fetch
and display the corresponding employee name, department, and salary.

Method 2: HLOOKUP
HLOOKUP is a function in Excel used to search for a value in the first row of a table (or array) and return a value
in the same column from a row you specify. Here’s how to use HLOOKUP in Excel:
Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
• lookup_value: The value you want to search for.

• table_array: The range of cells that contains the data you want to search in. The first row of this range should
contain the lookup values.
• row_index_num: The row number in the table_array from which to return the value.
• [range_lookup] (optional): A logical value that specifies whether you want an exact match or an approximate
match. If TRUE or omitted, it will find an approximate match. If FALSE, it will find an exact match.

TASK 1: Create a dataset with columns Product, Samsung_Price, Apple_Price, and Realme_Price. Use
HLOOKUP to find the price of a specific product in row 2.

Step 1: Create the Dataset

Product Laptop Smartphone Tablet
SamSunng_Price 80000 100000 60000
Apple_price 120000 125000 80000

Realme_Price 50000 90000 60000

Step 2: Use HLOOKUP for Price Lookup

Assuming you want to find the price of the product “Smartphone,” here is the HLOOKUP formula:
=HLOOKUP(C1,A1:D4,2,FALSE)

435
CITS : IT & ITES - Computer Software Application - Exercise 72