ASSIGNMENT – 48

USE OF XLOOKUP – ADVANCED LOOKUP PRACTICE

Objective

In this assignment, you will learn how to use the modern Excel XLOOKUP function for:

  • Exact Lookup

  • Left Lookup

  • Right Lookup

  • Lookup with Not Found Message

  • Multiple Column Lookup

  • Searching by Employee ID

  • Searching by Employee Name

  • Searching by Salary

  • Practical Employee Report

EMPLOYEE DATA

Emp ID Employee Name Department City Salary Experience Rating
101 Aarav Sales Sirsa 35000 3 4.2
102 Meera HR Hisar 42000 5 4.5
103 Rahul IT Sirsa 50000 7 4.8
104 Pooja Sales Delhi 46000 4 4.6
105 Karan HR Hisar 38000 2 3.8
106 Simran IT Sirsa 55000 6 4.7
107 Mohit Sales Delhi 62000 8 4.9
108 Neha HR Hisar 44000 4 4.3
109 Arjun IT Sirsa 48000 1 3.9
110 Priya Sales Delhi 52000 5 4.4
111 Rohan IT Sirsa 65000 9 4.9
112 Ananya HR Hisar 47000 6 4.6
113 Vikas IT Delhi 40000 3 4.0
114 Riya Sales Sirsa 51000 4 4.7
115 Nitin HR Hisar 35000 7 3.7

LOOKUP VALUES

Use these Employee IDs for practice:

Search ID
103
106
110
114
115
101
111
108
120

QUESTIONS

Q1. Find Employee Name

Use XLOOKUP to find the Employee Name for each Search ID.

Function:

XLOOKUP

Q2. Find Department

Use XLOOKUP to find the Department of each employee.

Q3. Find City

Use XLOOKUP to find the City of each employee.

Q4. Find Salary

Use XLOOKUP to find the Salary of each employee.

Q5. Find Experience

Use XLOOKUP to find the Experience of each employee.

Q6. Find Rating

Use XLOOKUP to find the Rating of each employee.

Q7. Display “Employee Not Found”

For Search ID 120, use XLOOKUP and display:

“Employee Not Found”

instead of #N/A.

Q8. Lookup Employee ID Using Employee Name

Find the Employee ID using Employee Name.

Examples:

  • Rahul → 103

  • Pooja → 104

  • Rohan → 111

Use XLOOKUP.

Q9. Find Employee Name Using Salary

Find the Employee Name whose Salary is:

₹62,000

Use XLOOKUP.

Q10. Find Employee Name Using Rating

Find the Employee Name whose Rating is:

4.9

Observe what happens because more than one employee has a rating of 4.9.

BONUS CHALLENGE ⭐

Q11.

Find the Salary of Simran using XLOOKUP.

Q12.

Find the Department of Rohan using XLOOKUP.

Q13.

Find the City of Priya using XLOOKUP.

Q14.

Find the Employee Name whose Experience is 7 years.

Q15. FINAL CHALLENGE

Create a complete Employee Lookup Report:

Search ID Employee Name Department City Salary Experience Rating
103            
106            
110            
114            
115            
101            
111            
108            
120            

Use XLOOKUP for all columns.

For Employee ID 120, display:

Employee Not Found

FORMULA PRACTICE

Basic XLOOKUP Syntax

=XLOOKUP(lookup_value,lookup_array,return_array)

Example – Find Employee Name

=XLOOKUP(I2,$A$2:$A$16,$B$2:$B$16)

Where:

  • I2 = Search ID

  • A2:A16 = Employee ID

  • B2:B16 = Employee Name

XLOOKUP with Not Found Message

=XLOOKUP(I2,$A$2:$A$16,$B$2:$B$16,"Employee Not Found")

If the ID does not exist, Excel displays:

Employee Not Found

Find Salary

=XLOOKUP(I2,$A$2:$A$16,$E$2:$E$16,"Salary Not Found")

Lookup Employee ID from Employee Name

=XLOOKUP(I2,$B$2:$B$16,$A$2:$A$16,"Employee Not Found")

Important:

Notice that the lookup column can be anywhere.

XLOOKUP can easily perform:

Left Lookup ←

as well as:

Right Lookup →

XLOOKUP VS VLOOKUP VS INDEX + MATCH

Feature VLOOKUP INDEX + MATCH XLOOKUP
Exact Match Yes Yes Yes
Left Lookup No Yes Yes
Not Found Message Requires IFERROR Requires IFERROR Built-in
Column Number Required Yes No No
Approximate Match Yes Yes Yes
Modern Excel Function Older Older Yes

IMPORTANT XLOOKUP ADVANTAGE

With VLOOKUP:

=VLOOKUP(I2,A2:G16,5,FALSE)

You must specify the column number.

With XLOOKUP:

=XLOOKUP(I2,A2:A16,E2:E16)

You directly specify:

Lookup Column → Return Column

This makes XLOOKUP easier and more flexible.

STUDENT TASK

Complete all 15 questions using XLOOKUP.

Use exact matching wherever appropriate and use the Not Found argument instead of allowing #N/A to appear in the final report.

PreviousNext