How do I Extract Multiple Rows using Index and Match

[Nikki225]

I have a spreadsheet where I'm tracking Employee Salary History information on one sheet and have a summary page on another sheet.

There are 3 columns on the Employee Salary History Information Sheet: Date, Description and Amount

On the Summary Page, I have a drop down box in cell B1 that allows me to select the employee name and I use VLookups to pull in the Hire Date, DOB and other relevant employee information.

I'd like to add an area to the bottom of this that will pull in the Date, Description and Amount from the Employee Salary History Information Sheet for the specific employee.

I don't have experience with array formulas and it seems like this would be the best solution for what I'm looking for.

Could you please help me with the formula?

 

[XLfriend777]

Hi Nikki225 - Welcome to the forum. Try this LINK

Click on View As: One Page

It has a very good explanation of INDEX and MATCH used together. Hope this helps.

 

[Nikki225]

Hi Nikki225 - Welcome to the forum. Try this LINK

Click on View As: One Page

It has a very good explanation of INDEX and MATCH used together. Hope this helps.

Thanks for the response. Where I need additional explanation is how do I change the formula when there are multiple matches?

 

[XLfriend777]

Hi Nikki225 - Try this LINK To copy down an Array formula I had to hold CTRL. Good Luck.

 

[Nikki225]

Do you know how I would modify that to pull in all 3 columns from the row with the matching criteria? For that employee name matching the criteria, I'd like it to show the date, description and amount of the salary change.

This link Extract all rows from a range that meet criteria in one column in excel | Get Digital Help - Microsoft Excel resource is closest to what I'm trying to do but I want an exact match to one criteria. Not a range. I can't figure out how to modify the formula.

 

[Nikki225]

Ok I finally figured it out!

=IFERROR(INDEX(salary,SMALL(IF($B$1=Sheet3!$B$2:$B$25,ROW(Sheet3!$B$2:$B$25)-MIN(ROW(Sheet3!$B$2:$B$25))+1,""),ROW(A7)),COLUMN(A7)),"")

I wasn't completely understanding the syntax and how to move it to the other columns.

 

[Aladin Akyurek]

Ok I finally figured it out!

=IFERROR(INDEX(salary,SMALL(IF($B$1=Sheet3!$B$2:$B$25,ROW(Sheet3!$B$2:$B$25)-MIN(ROW(Sheet3!$B$2:$B$25))+1,""),ROW(A7)),COLUMN(A7)),"")

I wasn't completely understanding the syntax and how to move it to the other columns.

I don't think this is a well-devised formula...

In which cell are you implementing this formula?

 

[Nikki225]

My salary history table is on sheet 3 and I'm using this formula on sheet 1 starting in column A, row 10.

 

[Aladin Akyurek]

My salary history table is on sheet 3 and I'm using this formula on sheet 1 starting in column A, row 10.

Thanks. What is the exact reference of the named range salary?

 

[Nikki225]

On sheet 3, it is A2:D25. The range excludes the headers but the 4 columns contain: Date, Employee, Description and Amount