Excel Power Search: Learn INDEX, MATCH & XMATCH Like a Pro

 

Welcome to this Excel tutorial focused on lookup techniques using INDEX, MATCH, and XMATCH functions. These functions allow you to search and extract information more accurately and flexibly than traditional VLOOKUP. You will learn how to perform powerful lookups using multiple criteria, return results from any direction, locate top or lowest performers, and even build dynamic search systems.

We will work with multiple examples from general business data such as Departments, Roles, Performance Scores, Salaries, Employee Names, Contact Details, and Locations. This approach helps you understand how these functions apply to real-life situations like record searching, reporting, and automated dashboards.

Table of Contents Find Salary by Employee ID.. 2 Find the Name of the Highest Rated Employee. 3 Find the Job Role of the Lowest Paid Employee. 4 Return the Rating of the First HR Manager. 4 Find Experience Based on Employee Name. 5 Get the Salary of the 3rd Highest Paid Employee. 6 Lookup Salary Using Partial Text 6 Return the Closest Rating Match.. 7 Lookup Starting from the Bottom... 8 Find HR Manager with Maximum Experience. 9 Retrieve Full Employee Record.. 9 Find Highest Salary Among Trainers. 10 Find Job Role Based on Minimum Experience Requirement 11 Return the Employee with Second Highest Rating.. 12 Coordinator with Salary Just Below Average. 12 Conclusion.. 13

 

.Dataset Used for All Examples

The dataset represents an employee records, stored in cells A1:G11 in Excel.

Note: Ensure your data is in cells A1:G11 for the formulas to work as written. Adjust cell references if your data is in a different range.

Find Salary by Employee ID

An HR manager needs to quickly find an employee’s salary by entering their Employee ID in a cell (e.g., H2). This is useful for payroll verification or quick reference during performance reviews. The ID might not be in the leftmost column, making VLOOKUP unsuitable.

Formula: =INDEX(E2:E11, MATCH(H2, A2:A11, 0))

Explanation:

• INDEX(E2:E11, ...): Retrieves a value from the Salary column (E2:E11). The row number is determined by the MATCH function.
• MATCH(H2, A2:A11, 0): Searches for the value in H2 (e.g., “E001”) in the Emp ID column (A2:A11). The “0” ensures an exact match.
• The formula finds the row where the ID matches and returns the corresponding salary.

Step-by-Step:

1. Enter an Employee ID (e.g., “E001”) in cell H2.
2. In another cell (e.g., I2), enter the formula =INDEX(E2:E11, MATCH(H2, A2:A11, 0)).
3. If H2 contains “E001”, the formula returns 65,000 (Ali’s salary).
4. Try different IDs in H2 to test the formula.

Why It’s Useful: Unlike VLOOKUP, which requires the lookup column to be on the left, INDEX-MATCH works regardless of column order, making it more flexible for HR datasets.

Find the Name of the Highest Rated Employee

During an annual performance review, the HR team wants to identify the employee with the highest performance rating to recognize them in a company meeting. This requires finding the maximum rating and the corresponding employee name.

Formula: =INDEX(B2:B11, MATCH(MAX(G2:G11), G2:G11, 0))

Explanation:

• MAX(G2:G11): Finds the highest value in the Rating column (4.8 in this case).
• MATCH(MAX(G2:G11), G2:G11, 0): Locates the row where 4.8 appears in the Rating column.
• INDEX(B2:B11, ...): Returns the employee name from the Name column (B2:B11) in that row.

Step-by-Step:

1. In a cell (e.g., I2), enter the formula =INDEX(B2:B11, MATCH(MAX(G2:G11), G2:G11, 0)).
2. The formula returns “Sana” (the employee with the highest rating of 4.8).
3. If multiple employees tie for the highest rating, it returns the first match.

Why It’s Useful: This formula automates identifying top performers, saving time during performance evaluations or award nominations.

Find the Job Role of the Lowest Paid Employee

The HR department is reviewing budget allocations and wants to identify the job role of the employee with the lowest salary to assess potential pay adjustments or reassignments.

Formula: =INDEX(D2:D11, MATCH(MIN(E2:E11), E2:E11, 0))

Explanation:

• MIN(E2:E11): Finds the lowest salary (45,000 in this case).
• MATCH(MIN(E2:E11), E2:E11, 0): Identifies the row where 45,000 appears in the Salary column.
• INDEX(D2:D11, ...): Returns the job role from the Job Role column (D2:D11) in that row.

Step-by-Step:

1. Enter the formula =INDEX(D2:D11, MATCH(MIN(E2:E11), E2:E11, 0)) in a cell (e.g., I2).
2. The formula returns “Assistant” (Asif’s job role, with the lowest salary of 45,000).
3. Test by modifying salaries to see how the result updates.

Why It’s Useful: This helps HR pinpoint roles with lower compensation for salary reviews or budget planning.

Return the Rating of the First HR Manager

The HR director wants to check the performance rating of the first HR Manager listed in the dataset to ensure leadership standards are met. A text-based lookup is needed to find this information.

Formula: =INDEX(G2:G11, MATCH("HR Manager", D2:D11, 0))

Explanation:

• MATCH("HR Manager", D2:D11, 0): Searches for “HR Manager” in the Job Role column (D2:D11) and returns the row number of the first match.
• INDEX(G2:G11, ...): Retrieves the rating from the Rating column (G2:G11) in that row.

Step-by-Step:

1. Enter the formula =INDEX(G2:G11, MATCH("HR Manager", D2:D11, 0)) in a cell (e.g., I2).
2. The formula returns 4.7 (Sara’s rating, the first HR Manager).
3. If no match is found, the formula returns an error (#N/A).

Why It’s Useful: This is ideal for quickly retrieving data based on text criteria, such as job titles, without manually scanning the dataset.

Find Experience Based on Employee Name

An HR analyst is preparing a report and needs to check an employee’s years of experience by entering their name in a cell (e.g., H2). The name column is not on the left, so VLOOKUP won’t work.

Formula: =INDEX(F2:F11, MATCH(H2, B2:B11, 0))

Explanation:

• MATCH(H2, B2:B11, 0): Finds the row where the name in H2 (e.g., “Kamran”) appears in the Name column (B2:B11).
• INDEX(F2:F11, ...): Returns the experience from the Experience column (F2:F11) in that row.

Step-by-Step:

1. Enter a name (e.g., “Kamran”) in cell H2.
2. In another cell (e.g., I2), enter =INDEX(F2:F11, MATCH(H2, B2:B11, 0)).
3. The formula returns 4 (Kamran’s experience).
4. Test with different names in H2.

Why It’s Useful: This performs a “left lookup” (looking up a value to the right of the lookup column), which VLOOKUP cannot do, making it versatile for HR data analysis.

Get the Salary of the 3rd Highest Paid Employee

The HR team is analyzing salary tiers and wants to identify the salary of the employee with the third highest pay to understand compensation distribution for mid level roles.

Formula: =INDEX(E2:E11, XMATCH(LARGE(E2:E11, 3), E2:E11))

Explanation:

• LARGE(E2:E11, 3): Finds the 3rd highest salary (75,000 in this case, Zainab’s salary).
• XMATCH(LARGE(E2:E11, 3), E2:E11): Locates the row where 75,000 appears in the Salary column. XMATCH is used for its modern functionality and clarity.
• INDEX(E2:E11, ...): Returns the salary from the Salary column (E2:E11) in that row.

Step-by-Step:

1. Enter the formula =INDEX(E2:E11, XMATCH(LARGE(E2:E11, 3), E2:E11)) in a cell (e.g., I2).
2. The formula returns 75,000 (Zainab’s salary, the 3rd highest).
3. Change the “3” in LARGE to find other ranks (e.g., 2 for 2nd highest).

Why It’s Useful: This helps HR analyze salary distribution and identify employees at specific pay levels for compensation planning.

Lookup Salary Using Partial Text

An HR assistant only remembers part of an employee’s name (e.g., “Sa” for Sara or Sana) and needs to find their salary. A wildcard search can handle partial text matches.

Formula: =INDEX(E2:E11, XMATCH("*"&H2&"*", B2:B11, 2))

Explanation:

• ""&H2&"": Creates a wildcard pattern (e.g., “Sa” matches any name containing “Sa”).
• XMATCH(""&H2&"", B2:B11, 2): Searches the Name column (B2:B11) for the partial text in H2, with “2” enabling wildcard matching.
• INDEX(E2:E11, ...): Returns the salary from the Salary column (E2:E11) in the matching row.

Step-by-Step:

1. Enter a partial name (e.g., “Sa”) in cell H2.
2. In another cell (e.g., I2), enter =INDEX(E2:E11, XMATCH("*"&H2&"*", B2:B11, 2)).
3. The formula returns 95,000 (Sara’s salary, the first match for “Sa”).
4. Test with other partial names like “Ali” or “Ri”.

Why It’s Useful: Wildcard searches are perfect when exact data isn’t available, such as when HR staff work with incomplete information.

Return the Closest Rating Match

The HR manager wants to find the employee whose rating is closest to a target value (e.g., 4.2 in H2) for benchmarking performance. An approximate match is needed if the exact rating doesn’t exist.

Formula: =INDEX(G2:G11, XMATCH(H2, G2:G11, 1))

Explanation:

• XMATCH(H2, G2:G11, 1): Searches for the value in H2 (e.g., 4.2) in the Rating column (G2:G11). The “1” indicates an approximate match, returning the closest lower value if no exact match exists.
• INDEX(G2:G11, ...): Returns the rating from the Rating column in that row.

Step-by-Step:

1. Enter a target rating (e.g., 4.2) in cell H2.
2. In another cell (e.g., I2), enter =INDEX(G2:G11, XMATCH(H2, G2:G11, 1)).
3. The formula returns 4.1 (Kamran’s rating, the closest lower value to 4.2).
4. Test with different ratings in H2, like 4.5 or 3.7.

Why It’s Useful: Approximate matches are valuable for performance benchmarking or when exact data isn’t available.

Lookup Starting from the Bottom

The HR system logs duplicate Employee IDs due to updates, and the team wants the most recent record (the last occurrence in the list) for a given ID entered in H2.

Formula: =INDEX(D2:D11, XMATCH(H2, A2:A11, 0, -1))

Explanation:

• XMATCH(H2, A2:A11, 0, -1): Searches for the ID in H2 in the Emp ID column (A2:A11). The “0” ensures an exact match, and “-1” searches from the bottom up, returning the last match.
• INDEX(D2:D11, ...): Returns the job role from the Job Role column (D2:D11) in that row.

Step-by-Step:

1. Add a duplicate ID (e.g., “E001”) to the bottom of the dataset (row 11) with a different job role (e.g., “Manager”).
2. Enter “E001” in cell H2.
3. In another cell (e.g., I2), enter =INDEX(D2:D11, XMATCH(H2, A2:A11, 0, -1)).
4. The formula returns “Manager” (the last occurrence of E001).

Why It’s Useful: Reverse lookups are critical for systems with duplicate entries, ensuring HR retrieves the latest employee data.

Find HR Manager with Maximum Experience

The HR director is selecting a candidate for a leadership role and needs the name of the HR Manager with the most experience to consider for promotion.

Formula: =INDEX(B2:B11, XMATCH(MAX(F2:F11*(D2:D11="HR Manager")), F2:F11))

Explanation:

• F2:F11(D2:D11="HR Manager"): Multiplies the Experience column by a condition that checks if the Job Role is “HR Manager”. This creates an array where non-HR Managers get 0, and HR Managers keep their experience values.
• MAX(...): Finds the highest experience among HR Managers (7 for Sana).
• XMATCH(...): Locates the row where this maximum experience appears.
• INDEX(B2:B11, ...): Returns the name from the Name column.

Step-by-Step:

1. Enter the formula =INDEX(B2:B11, XMATCH(MAX(F2:F11*(D2:D11="HR Manager")), F2:F11)) in a cell (e.g., I2).
2. The formula returns “Sana” (HR Manager with 7 years of experience).
3. Ensure array formulas are supported (Excel 365/2021 automatically handles this).

Why It’s Useful: This combines multiple criteria (job role and experience) to pinpoint specific employees, ideal for leadership selection.

Retrieve Full Employee Record

An HR dashboard requires displaying all details (ID, Name, Department, Job Role, Salary, Experience, Rating) for an employee when their name is entered in H2, streamlining data retrieval.

Formula: =INDEX(A2:G11, MATCH(H2, B2:B11, 0), )

Explanation:

• MATCH(H2, B2:B11, 0): Finds the row where the name in H2 appears in the Name column.
• INDEX(A2:G11, ..., ): Returns the entire row (all columns from A to G) for that employee. The empty column argument in INDEX spills all columns in modern Excel.

Step-by-Step:

1. Enter a name (e.g., “Rida”) in cell H2.
2. In a cell (e.g., I2), enter =INDEX(A2:G11, MATCH(H2, B2:B11, 0), ).
3. The formula spills the full record: E004, Rida, HR, Trainer, 72,000, 5, 4.3.
4. Ensure your Excel version supports dynamic arrays (Excel 365/2021).

Why It’s Useful: This creates dynamic HR forms or dashboards, displaying all employee details with a single input.

Find Highest Salary Among Trainers

The HR team is reviewing compensation for trainers and wants to identify the highest salary among employees in the Trainer role to set a benchmark.

Formula: =INDEX(E2:E11, MATCH(MAX(E2:E11*(D2:D11="Trainer")), E2:E11, 0))

Explanation:

• E2:E11(D2:D11="Trainer"): Multiplies the Salary column by a condition that checks for “Trainer” in the Job Role column, zeroing out non-Trainer salaries.
• MAX(...): Finds the highest salary among Trainers (75,000 for Zainab).
• MATCH(...): Locates the row of this salary.
• INDEX(E2:E11, ...): Returns the salary.

Step-by-Step:

1. Enter the formula =INDEX(E2:E11, MATCH(MAX(E2:E11*(D2:D11="Trainer")), E2:E11, 0)) in a cell (e.g., I2).
2. The formula returns 75,000 (Zainab’s salary).
3. Test by changing Trainer salaries to verify the result.

Why It’s Useful: This filters data by role and finds key metrics, useful for role-specific compensation analysis.

Find Job Role Based on Minimum Experience Requirement

HR is hiring for a project requiring at least 5 years of experience and needs the job role of the first employee meeting this criterion to assess suitability.

Formula: =INDEX(D2:D11, MATCH(TRUE, F2:F11>=H2, 0))

Explanation:

• F2:F11>=H2: Creates an array of TRUE/FALSE values where TRUE indicates employees with experience greater than or equal to the value in H2 (e.g., 5).
• MATCH(TRUE, ..., 0): Finds the first row where TRUE appears (first employee with experience >= 5).
• INDEX(D2:D11, ...): Returns the job role from that row.

Step-by-Step:

1. Enter 5 in cell H2.
2. In another cell (e.g., I2), enter =INDEX(D2:D11, MATCH(TRUE, F2:F11>=H2, 0)).
3. The formula returns “Trainer” (Rida, the first employee with 5+ years).
4. Press Ctrl+Shift+Enter in older Excel versions (pre-365) as this is an array formula.

Why It’s Useful: This helps HR filter employees by minimum qualifications, streamlining recruitment or assignment processes.

Return the Employee with Second Highest Rating

The HR team is preparing awards and wants to recognize the employee with the second-highest performance rating as a runner-up, ensuring fair acknowledgment.

Formula: =INDEX(B2:B11, MATCH(LARGE(G2:G11, 2), G2:G11, 0))

Explanation:

• LARGE(G2:G11, 2): Finds the 2nd highest rating (4.7 for Sara).
• MATCH(LARGE(G2:G11, 2), G2:G11, 0): Locates the row of this rating.
• INDEX(B2:B11, ...): Returns the employee name.

Step-by-Step:

1. Enter the formula =INDEX(B2:B11, MATCH(LARGE(G2:G11, 2), G2:G11, 0)) in a cell (e.g., I2).
2. The formula returns “Sara” (second-highest rating of 4.7).
3. Change the “2” in LARGE to find other ranks (e.g., 3 for 3rd highest).

Why It’s Useful: This identifies secondary top performers, useful for awards, promotions, or performance tracking.

Coordinator with Salary Just Below Average

The HR department is reviewing salaries and wants to identify a Coordinator whose salary is just below the average of all employees to consider for a pay adjustment.

Formula: =INDEX(B2:B11, MATCH(1, (E2:E11<AVERAGE(E2:E11))*(D2:D11="Coordinator"), 0)

Explanation:

• AVERAGE(E2:E11): Calculates the average salary (67,700).
• (E2:E11<AVERAGE(E2:E11))*(D2:D11="Coordinator"): Creates an array where TRUE indicates Coordinators with salaries below 67,700.
• MATCH(1, ..., 0): Finds the first row where both conditions are TRUE.
• INDEX(B2:B11, ...): Returns the employee name.

Step-by-Step:

1. Enter the formula =INDEX(B2:B11, MATCH(TRUE, (E2:E11<AVERAGE(E2:E11))*(D2:D11="Coordinator"), 0)) in a cell (e.g., I2).
2. The formula returns “Ahmed” (Coordinator with salary 55,000, below average).
3. Press Ctrl+Shift+Enter in older Excel versions for array formulas.

Why It’s Useful: This combines multiple conditions to identify specific employees for salary reviews or adjustments.

Conclusion

INDEX, MATCH, and XMATCH are powerful lookup functions that help you search, extract, and analyze data more accurately in Excel. By learning these advanced techniques, you can build smarter reports, automate searches, and work faster with any dataset. Practice these formulas regularly to improve your skills and boost your productivity in Excel.