📊 Excel Project – Lookup Formulas (VLOOKUP &
XLOOKUP)
Project Title: Inventory Management & Price Lookup
Evaluation Using: VLOOKUP & XLOOKUP Formulas
Section A — Product Pricing & Details (Use VLOOKUP)
You will need two sheets for this section: ProductData and LookupTable.
Sheet 1: ProductData (Source Data)
Product ID Product Name Price Supplier Location
(A) (B) (C) (D) (E)
P101 Laptop Pro 1200 S001 Aisle 1
P102 Keyboard RGB 60 S002 Aisle 2
P103 Mouse Wireless 25 S001 Aisle 1
P104 Monitor 27" 350 S003 Aisle 3
P105 Webcam 4K 85 S002 Aisle 2
Sheet 2: LookupTable (Where you will perform the lookups)
Item (A) Lookup Value (B) Formula Cell (C)
Laptop Pro P101 C2
Monitor 27" P104 C3
Projector (Not Found) P109 C4
Questions (VLOOKUP)
1. Basic Price Lookup: Write a VLOOKUP formula in cell C2 of LookupTable to
find the Price of the product with Product ID P101 from the ProductData sheet.
(Use Exact Match).
2. Supplier Lookup: Write a VLOOKUP formula in cell C3 of LookupTable to find
the Supplier ID for Product ID P104.
3. Error Handling: Write a VLOOKUP formula in cell C4 to find the Price for
Product ID P109. If the ID is not found, use IFERROR to display "ID
Missing" instead of the default error.
Section B — Commission and Discount Bands (Use VLOOKUP)
Sheet 3: CommissionBands (Source Data)
Sales Value (A) Commission Rate (B)
0 0.00%
Sales Value (A) Commission Rate (B)
5000 5.00%
15000 7.50%
30000 10.00%
Sheet 4: SalesData (Where you will perform the lookups)
Agent (A) Sales (B) Formula Cell (C)
Agent A 18000 C2
Agent B 4500 C3
Agent C 35000 C4
Questions (VLOOKUP - Approximate Match)
4. Commission Rate Lookup: Write a VLOOKUP formula in cell C2 of SalesData
to find the correct Commission Rate for a sale of $18,000 using the
CommissionBands table. (Use Approximate Match).
5. Bonus Calculation: Write a formula in cell C3 to find the Commission Rate
for a sale of $4,500 and then calculate the actual Commission Amount
(Sales * Rate).
6. Highest Band: Write a VLOOKUP formula in cell C4 to find the Commission
Rate for a sale of $35,000.
Section C — Advanced Lookups (Use XLOOKUP)
Use the original Sheet 1: ProductData from Section A, but assume the columns are
rearranged:
Supplier Location Price Product Name Product ID
(A) (B) (C) (D) (E)
S001 Aisle 1 1200 Laptop Pro P101
S002 Aisle 2 60 Keyboard RGB P102
S001 Aisle 1 25 Mouse Wireless P103
Sheet 5: XLookupTests (Where you will perform the lookups)
Item (A) Lookup Value (B) Formula Cell (C)
Laptop Pro P101 C2
Keyboard RGB S002 C3
Product 404 P404 C4
Questions (XLOOKUP)
7. Right-to-Left Lookup: Write an XLOOKUP formula in cell C2 of XLookupTests
to find the Supplier ID (Column A) by searching for the Product ID P101
(Column E). (Demonstrates XLOOKUP's flexibility).
8. Multiple Results Spill: Write an XLOOKUP formula in cell C3 to find
Supplier ID S002 and return both its Location (Column B) and Price
(Column C) using a single formula.
9. Custom Error Handling: Write an XLOOKUP formula in cell C4 to find the
Price for Product ID P404. If the ID is not found, use the dedicated
if_not_found argument to display "Out of Stock".
Section D — Combined & Directional Lookups (Use XLOOKUP)
Use the original Sheet 1: ProductData from Section A, but add one duplicate entry.
Sheet 6: TrackingData (Source Data)
Product ID (A) Transaction Date (B) Status (C)
T10 2025-01-05 Pending
T11 2025-01-10 Shipped
T10 2025-01-15 Delivered
Sheet 7: AdvancedTests (Where you will perform the lookups)
Item (A) Lookup Value (B) Formula Cell (C)
T11 Status T11 C2
T10 Latest Status T10 C3
T10 First Date T10 C4
Questions (XLOOKUP - Advanced Modes)
10. Basic XLOOKUP: Write an XLOOKUP formula in cell C2 of AdvancedTests to
find the Status for Transaction ID T11.
11. Find Last Match: Write an XLOOKUP formula in cell C3 to find the Status
for Transaction ID T10, but ensure it returns the last or most recent match
("Delivered"). (Use the appropriate search_mode argument: -1).
12. Find First Match: Write an XLOOKUP formula in cell C4 to find the
Transaction Date for Transaction ID T10, ensuring it returns the first match
(2025-01-05). (Use the appropriate search_mode argument: 1).
Section E — Mixed/Review (VLOOKUP vs. XLOOKUP)
Question
Scenario Objective
No.
VLOOKUP Failure: In ProductData
Demonstrating
(Section A), if the columns were
VLOOKUP's limitation
13. arranged as Price (A), Name (B), ID
(lookup column must
(C). Write a formula using VLOOKUP
be the first column).
that attempts to find the Product
Question
Scenario Objective
No.
Name using the Price $1200 as the
lookup value. Explain why it fails.
XLOOKUP Wildcard: Using the
ProductData table, write an XLOOKUP
formula to find the Price of any Using XLOOKUP with
14. product whose Product Name wildcards for partial
contains the word "Laptop". (Hint: match.
Use the wildcard character * and the
appropriate match_mode).
Two-Way Lookup Concept (Bonus):
If you had a table with Months across Conceptual
the top row and Product IDs down understanding of
the first column, which two functions nesting lookups (e.g.,
15.
would you nest together to find the XLOOKUP and
sales figure for Product P102 in XMATCH or INDEX
March? (Write the names of the two and MATCH).
functions).