Lookup Functions
Introduction: Why Lookup Functions Matter?
Imagine this:
-
You have a student’s name and want their marks.
-
You know a product code and want to pull its price.
-
You want to create dashboards where values change automatically based on selection.
That’s where lookup functions shine — they help you search data in a table and pull matching values.
Step 1: Create This Sample Dataset
| Student ID | Name | Subject 1 | Subject 2 | Total Marks |
|---|---|---|---|---|
| 101 | Aayush | 78 | 82 | 160 |
| 102 | Riya | 55 | 68 | 123 |
| 103 | Aman | 39 | 43 | 82 |
| 104 | Neha | 91 | 87 | 178 |
| 105 | Krish | 65 | 71 | 136 |
Now below or beside the table, we’ll try to fetch details dynamically based on input using different lookup functions.
Step 2: VLOOKUP(search_key, range, index, [is_sorted])
Use Case: Get total marks by entering Student ID.
| Enter Student ID | 103 |
|---|---|
| Total Marks |
📍 Formula to place under "Total Marks":
=VLOOKUP(B10, A2:E6, 5, FALSE)
🧠 It searches for the value in B10 (103) in the first column of A2:E6, then returns the value from the 5th column (Total Marks).
⚠ VLOOKUP always searches in the first column of the range and looks right only.
Step 3: XLOOKUP(search_key, lookup_range, return_range, [if_not_found])
✅ XLOOKUP is more flexible and preferred when available.
Use Case: Search by name and get Subject 1 marks.
| Enter Name | Aman |
|---|---|
| Subject 1 |
📍 Formula:
=XLOOKUP(B13, B2:B6, C2:C6, "Not Found")
🧠 It searches B13 (Aman) in the Name column (B2:B6) and returns the value from Subject 1 column (C2:C6).
Works left or right, unlike VLOOKUP.
Step 4: MATCH(search_key, range, [match_type])
Use Case: Find the position of "Neha" in the Name column.
📍 Formula:
=MATCH("Neha", B2:B6, 0)
This will return 4, meaning Neha is at the 4th row in B2:B6.
Used to find position, not value.
Step 5: INDEX(reference, row_num, [column_num])
Use Case: Get the marks from Subject 2 for the 3rd student in the list.
📍 Formula:
=INDEX(D2:D6, 3)
Returns the 3rd value from Subject 2 column (D2:D6), i.e., 43
Now combine INDEX and MATCH together 👇
Step 6: Combine INDEX + MATCH (VLOOKUP Alternative)
Use Case: Get Total Marks using Name (instead of VLOOKUP).
📍 Formula:
=INDEX(E2:E6, MATCH("Neha", B2:B6, 0))
🧠 It finds the row number where "Neha" exists, and fetches value from corresponding row of Total Marks.
More dynamic than VLOOKUP because you can look in any direction and even pull from the left.
Mini Project: Dynamic Student Lookup Tool
-
Create a student lookup section with input for:
-
Student ID
-
Student Name
-
-
Auto-populate:
-
Subject 1 Marks (using XLOOKUP)
-
Subject 2 Marks (using XLOOKUP)
-
Total Marks (using INDEX + MATCH)
-
Position in List (using MATCH)
-
