TL;DR: Learn what makes VLOOKUP, INDEX, and MATCH powerful in Excel — and how AmrutVlookup (a custom-built UDF) takes it to the next level with smarter, flexible lookups.
Author: Amrut Chitragar
Published on: May 23, 2025
📌 Disclaimer: This article is for educational purposes only and not professional tax advice. Please consult a qualified expert for your unique situation.
What Is Lookup in Excel? And Why Baap of Match, INDEX, VLOOKUP & AmrutVlookup Are Game-Changers
In Excel, lookup functions are used to search and retrieve specific data from large datasets. Imagine you have a list of 1,000 employees. If you type an employee ID like EMP1025, Excel can instantly display their name, department, salary, and more — thanks to lookup formulas.
The three most commonly used formulas are VLOOKUP, INDEX, and MATCH. Let’s explore each.
1. VLOOKUP – The Most Common Lookup Tool
- 🔹 Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) - 🔹 Searches in the first column and returns data from a specified column
- 🔹 Limitation: Cannot look to the left; breaks if columns rearranged
🎯 How VLOOKUP Works – Example
Below is an example to find the EMP NAME for EMP-09 using VLOOKUP. The column and row rulers are shown as in Excel.
| A | B | C | D | E | F |
| 1 | ||||||
| 2 | EMP ID | EMP NAME | SALARY | OT | TOTAL | |
| 3 | EMP-01 | ABC-01 | 11,500.00 | 305.00 | 11,805.00 | |
| 4 | EMP-02 | ABC-02 | 13,000.00 | 430.00 | 13,430.00 | |
| 5 | EMP-03 | ABC-03 | 14,500.00 | 555.00 | 15,055.00 | |
| 6 | EMP-04 | ABC-04 | 16,000.00 | 680.00 | 16,680.00 | |
| 7 | EMP-05 | ABC-05 | 17,500.00 | 805.00 | 18,305.00 | |
| 8 | EMP-06 | ABC-06 | 19,000.00 | 930.00 | 19,930.00 | |
| 9 | EMP-07 | ABC-07 | 20,500.00 | 1,055.00 | 21,555.00 | |
| 10 | EMP-08 | ABC-08 | 22,000.00 | 1,180.00 | 23,180.00 | |
| 11 | EMP-09 | ABC-09 | 23,500.00 | 1,305.00 | 24,805.00 | |
| 12 | ||||||
| 13 | EMP ID | EMP NAME | SALARY | OT | TOTAL | |
| 14 | EMP-09 | =VLOOKUP(A11,A2:E11,2,FALSE) | ||||
📘 Formula Breakdown: =VLOOKUP(A11, A2:E11, 2, FALSE)
- A11 → Lookup Value: This is the value to search for. In this case,
EMP-09. - A2:E11 → Table Array: The range of cells where Excel will search. The first column must contain the lookup value.
- 2 → Column Index Number: Excel will return the value from the 2nd column in the range (which is EMP NAME).
- FALSE → Exact Match: Excel must find an exact match for
EMP-09. If not found, it returns an error.
✔️ This formula returns ABC-09 because EMP-09 is found in column A (row 11), and column 2 (EMP NAME) has ABC-09.
2. MATCH – Finds Position, Not Value
- 🔹 Syntax:
=MATCH(lookup_value, lookup_array, [match_type]) - 🔹 Returns the position of a value in a row or column
3. INDEX – Retrieves Value by Position
- 🔹 Syntax:
=INDEX(array, row_num, [column_num]) - 🔹 Works both vertically and horizontally
INDEX + MATCH = Baap of VLOOKUP
- 🔹
=INDEX(B2:D10, MATCH("EMP1025", A2:A10, 0), 2) - 🔹 Handles left lookups and dynamic ranges
Employee Salary Lookup Example (VLOOKUP vs INDEX & MATCH)
Excel-style Lookup Table with INDEX & MATCH
| Col‑1 | Col‑2 | Col‑3 | Col‑4 | Col‑5 | |
| A1 | EMP ID | EMP NAME | SALARY | OT | TOTAL |
| A2 | EMP‑01 | ABC‑01 | 11,500.00 | 305.00 | 11,805.00 |
| A3 | EMP‑02 | ABC‑02 | 13,000.00 | 430.00 | 13,430.00 |
| A4 | EMP‑03 | ABC‑03 | 14,500.00 | 555.00 | 15,055.00 |
| A5 | EMP‑04 | ABC‑04 | 16,000.00 | 680.00 | 16,680.00 |
| A6 | EMP‑05 | ABC‑05 | 17,500.00 | 805.00 | 18,305.00 |
| A7 | EMP‑06 | ABC‑06 | 19,000.00 | 930.00 | 19,930.00 |
| A8 | EMP‑07 | ABC‑07 | 20,500.00 | 1,055.00 | 21,555.00 |
| A9 | EMP‑08 | ABC‑08 | 22,000.00 | 1,180.00 | 23,180.00 |
| A10 | EMP‑09 | ABC‑09 | 23,500.00 | 1,305.00 | 24,805.00 |
| A12 | Enter EMP ID to Lookup: EMP‑09 | ||||
| A13 | EMP‑09 | ABC‑09 | 23,500.00 | 1,305.00 | 24,805.00 |
=INDEX($B$1:$B$11, MATCH(A13, $A$1:$A$11, 0))
Explanation:
🔹 A13 is the cell where EMP ID is entered (e.g., EMP‑09)
🔹 MATCH finds the row number where EMP‑09 exists in column A
🔹 INDEX returns the corresponding EMP NAME, SALARY, OT, and TOTAL from columns B to E
Introducing AmrutVlookup (UDF) – The Next Generation Lookup
- 🔹 Developed by Amrut, this User Defined Function (UDF) beats traditional formulas
- 🔹 Supports two-way lookups, conditions, and no column limits
Coming soon: Real examples, downloadable file, and VBA code of AmrutVlookup.
🔗 Related Posts:
Post a Comment