Scribd
Upload
2 views2 pages

Program 7

The document outlines a procedure for calculating data validation in MS Excel using VLOOKUP and HLOOKUP functions. It details the steps to create a student data structure, enter data, and apply the formulas to retrieve Total Score and Average based on student IDs. The results demonstrate effective management of data retrieval using these functions.

Uploaded by

apjharshavarthini
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views2 pages

Program 7

The document outlines a procedure for calculating data validation in MS Excel using VLOOKUP and HLOOKUP functions. It details the steps to create a student data structure, enter data, and apply the formulas to retrieve Total Score and Average based on student IDs. The results demonstrate effective management of data retrieval using these functions.

Uploaded by

apjharshavarthini
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

EX: 7 CALCULATION OF DATA VALIDATION USING VLOOKUP AND HLOOKUP

Aim:
To Calculate data validation using VLOOKUP and HLOOKUP.
Algorithm
STEP 1: To open a new MS Excel file, click on start → All programs → Microsoft
Office → Microsoft Excel.
Step 2: Create Data Structure:
Sheet1: Student Data
● Column A: Reg. No
● Column B: Name
● Column C: Total Score
● Column D: Average
● Column E: Grade
Step 3: Enter the data for each student in the respective columns.
Step 4: To find the Grade for a student based on their Average
Step 5: Using VLOOKUP, The VLOOKUP function is used to search for a value in
the first column of a range and return a value in the same row from another column.
To find the Total Score for a student based on their ID
1. Prepare the Lookup Cell:
Assume you want to look up the total score for a student ID entered in cell G2.
2. Enter the VLOOKUP Formula:
=VLOOKUP(G2, A2:E4, 3, FALSE)
Explanation:
● G2: The cell containing the student ID you want to look up.
● A2:E4: The range of the database.
● 3: The column index number for Total Score (C) in the range.
● FALSE: Specifies that you want an exact match.
3. Press Enter.
● The Total Score for the student ID in G2 will appear in H2.
Step 6: Using HLOOKUP, The HLOOKUP function is used to search for a value in
the top row of a range and return a value in the same column from another row.
To find the Total Score based on the Student ID header:
1. Set Up Your Data Horizontally:
● For HLOOKUP, your data should be organized horizontally.
Example Data Layout:
Sheet2: Horizontal Data
● Row 1: Headers (Student ID, Name, Total, Average, Grade)
● Row 2: Data for student 001
● Row 3: Data for student 002
● Row 4: Data for student 003
2. Enter the HLOOKUP Formula:
● In cell G2, where you enter the Student ID you want to look up.
To find the Average:
In cell I2, enter:
plaintext
Copy
code
=HLOOKUP (G2, A1:E4, 3, FALSE)
Press Enter.
● The Average will appear in I2.

RESULT:
Thus, Results effectively manage both the VLOOKUP and HLOOKUP in [Link].

You might also like