Question

Enter a nested INDEX and MATCH function in cell K7 that examines the range B6:H25 and returns the corresponding employee information based on the match values in cell J7 and cell K6. Note K6 contains a validation list that can be used to select various lookup categories. Use the Data Validation list in cell J7 to select Employee_ID 31461 and select Salary in cell K6 to test the function.

Answer 1

To retrieve employee information in Excel, use this formula in K7: =INDEX($B$6:$H$25, MATCH(J7, $B$6:$B$25, 0), MATCH(K6, $B$5:$H$5, 0)).

What does this formula do?

This formula relies on two MATCH functions nested within an INDEX function. The first MATCH finds the chosen Employee_ID in column B, while the second MATCH identifies the selected category (e.g., Salary) in the header row.

INDEX then fetches the corresponding data from the intersecting row and column within the specified range ($B$6:$H$25). Ensure your cell references match the data layout for accurate retrieval when selecting an Employee_ID in J7 and a category in K6.