# Two criteria Index Match on multiple sheets returning multiple values

1. ## Two criteria Index Match on multiple sheets returning multiple values

Hello,

I am new to Excel Forum and I am not very advanced in Excel. I hope someone can help me with this.

I am building a payroll workbook and I need to build a sheet that will allow me to choose an employee name and a date. The multiple values returning are the details of this particular employee and week of work. It could return multiple lines, as one employee can have two entries relating to the same week.

I found a formula that uses INDEX and MATCH which is giving good results, but my workbook is on multiple sheets (one sheet per month). I don't know how to build a formula that can look into all 12 sheets and return the appropriate data.

I have built a workbook example : Test formula on payroll.xlsx
The example is a simpler version with only 3 months. The "Payslip" sheet is where I need to build the formula.

2. ## Re: Two criteria Index Match on multiple sheets returning multiple values

Hello and Welcome to the forum !
First change your sheet name to complete month name i.e. Januray, February, March and so on then in paysilp sheet c14 copy paste below
then hold control and shift together and Hit enter to make it array formula ! then drag formula to right !

=IFERROR(INDEX(INDIRECT("'"&TEXT(\$A\$7,"MMMM")&"'!\$A\$2:\$G\$100"),MATCH(\$A\$3&\$A\$7,INDIRECT("'"&TEXT(\$A\$7,"MMMM")&"'!\$A\$2:\$A\$100")&INDIRECT("'"&TEXT(\$A\$7,"MMMM")&"'!\$B\$2:\$B\$100"),0),COLUMN(C3)),"Not Available")

3. ## Re: Two criteria Index Match on multiple sheets returning multiple values

Hi hemesh,

Thanks for your reply. Very interesting formula. It didnt do exactly what I need, because I need to return multiple values. But I took the following formula:

``Please Login or Register  to view this content.``
``Please Login or Register  to view this content.``
And it works great! Thanks.

I have another question, if I may. What if I want to do the same thing but only with one criteria that would return results that are on all the sheets. Per example, a table where we can choose one employee and get all the results for this employee trough out the year.

ps: i've tried to translate everything, but my sheets and formulas are in french on my computer. sorry if it make some confusion.

4. ## Re: Two criteria Index Match on multiple sheets returning multiple values

Hello all,

I have this other question explained above. Maybe it is best to create a new post. So I'll do that.

There are currently 1 users browsing this thread. (0 members and 1 guests)