Help indexing multiple sheets and matching multiple sheets

I can index and match across 2 sheets easily enough, but how can I index multiple sheets and match across those multiple sheets as well?

=IFERROR(INDEX(Defense!A:A,MATCH(RB!C2,Defense!B:B,0)),0)


I need to index Y on sheet 2,3,4,5 and then match B on 2,3,4,5 to B on sheet 1 which will paste in K on sheet 1.

I have tried various combinations of just adding index and match statements, but it fails.

The above formula is what I have tried to expand, because it works great with just 2 sheets.

You could do it like this (if you only have 5 sheets it's not too long a formula):

=IFERROR(INDEX(Defense!A:A,MATCH(RB!C2,Defense!B:B,0)),0)+IFERROR(INDEX(Sheet3!A:A,MATCH(RB!C2,Sheet3!B:B,0)),0)+IFERROR(INDEX(Sheet4!A:A,MATCH(RB!C2,Sheet4!B:B,0)),0)+IFERROR(INDEX(Sheet5!A:A,MATCH(RB!C2,Sheet5!B:B,0)),0)

I've assumed that Sheet2 is actually named Defense - you will need to change the names of the others sheets as appropriate (and put apostrophes around the sheet names if you have spaces in them).

I've also assumed that you are trying to return a number (as you have 0 at the end of the IFERROR term).

Hope this helps.

Pete

Yes it should return a number.

That formula partially works. Sheet 1 is lineup, sheet 2 is QB sheet 3 is RB sheet 4 is WR sheet 5 is TE.

On Sheet 1 is where all names are listed in column C. So the name on C2 lineup will be found in column C on one of the other sheets. When the match is found I need the data from the Y column on that sheet to be the result.

It helps if you give all the relevant information to begin with, or attach a sample workbook.

Where the formula says A:A you should change it to Y:Y, where it says B:B you should change it to C:C, where it says RB!C2 you can change this to just C2, and where it says Defense you can change this to Sheet2, then you can change the sheet names Sheet2 to Sheet5 to your actual names, i.e. QB, RB, WR, TE respectively.

As the column to return data from is to the right of the lookup column, you can use VLOOKUP instead, i.e.:

=IFERROR(VLOOKUP(C2,QB!C:Y,23,0),0)+IFERROR(VLOOKUP(C2,RB!C:Y,23,0),0)+IFERROR(VLOOKUP(C2,WR!C:Y,23,0),0)+IFERROR(VLOOKUP(C2,TE!C:Y,23,0),0)

then copy down as required.

Hope this helps.

Pete

Worked perfectly. Thank you very much.