Index and Match

[Hoss]

I am struggling to combine several index match functions with if, then statements (I think this is the way to do this). I have 35 different worksheets in a work book. The data to index is always starting in column C:IS and in rows 175, 341, 507, 673, 839, 1005 & 1171. The data to match is always in column F:IS and in rows 174, 340, 506, 672, 838, 1004 & 1170. These are the same in all worksheets in this workbook. What I want to accomplish is for it to look through the first rows (175,174) and if it doesn't find a match, it moves on the the second set of rows (341, 340) and so on.

Here are two of the formula's below. Any help or suggestions would be fantastic. Thanks

=INDEX('6'' & ADA Tables'!$F$839:$GH$839,MATCH('Task & Notes Master'!$A10,'6'' & ADA Tables'!$C$838:$GH$838,0)+2)

=INDEX('6'' & ADA Tables'!$F$175:$GH$175,MATCH('Task & Notes Master'!$A12,'6'' & ADA Tables'!$C$174:$GH$174,0)+2)

[NBVC]

Does this do it?

=INDEX('6'' & ADA Tables'!$F$174:$GH$1171,SUMPRODUCT(('6'' & ADA Tables'!$F$174:$GH$1171='Task & Notes Master'!$A10)*ROW('6'' & ADA Tables'!$F$174:$GH$1171))-ROW('6'' & ADA Tables'!$F$174)+2,SUMPRODUCT(('6'' & ADA Tables'!$F$174:$GH$1171='Task & Notes Master'!$A10)*COLUMN('6'' & ADA Tables'!$F$174:$GH$1171))-COLUMN('6'' & ADA Tables'!$F$174)+1)

[Hoss]

I'm not sure, as soon as I plugged that formula in, the spreadsheet stopped responding. I'll keep trying. I was hoping to stop it from searching through the rows that were not necessary to speed up the calculations.

[NBVC]

You could probably use a nested if then.... but you will be at the limit of 7 allowed.

[Hoss]

7 would be the most that I would need for some of the sheets. Some of the other sheets would not need that many. How would I set up the IF, Then formula?

[NBVC]

On 2nd thought... you may run out of nested function allowance before you use 7 ifs... maybe

Something like:

=TRIM(IF(ISNUMBER(MATCH('Task & Notes Master'!$A12,'6'' & ADA Tables'!$C$174:$GH$174,0)),INDEX('6'' & ADA Tables'!$F$175:$GH$175,MATCH('Task & Notes Master'!$A12,'6'' & ADA Tables'!$C$174:$GH$174,0)+2),"")&IF(ISNUMBER(MATCH('Task & Notes Master'!$A10,'6'' & ADA Tables'!$C$340:$GH$340,0)),INDEX('6'' & ADA Tables'!$F$341:$GH$341,MATCH('Task & Notes Master'!$A10,'6'' & ADA Tables'!$C$340:$GH$341,0)+2),"")&IF(ISNUMBER(MATCH.....)))

It might get a little long... you need to continue where I left off separating each statement with the & symbol and enveloping the whole formula in a Trim() function.

[Hoss]

That works to a point, but the formula is too long to get past 839,838

[NBVC]

How about this one?

=INDEX('6'' & ADA Tables'!$F$174:$GH$1171,MAX(('6'' & ADA Tables'!$F$174:$GH$1171='Task & Notes Master'!$A10)*ROW('6'' & ADA Tables'!$F$174:$GH$1171))-ROW('6'' & ADA Tables'!$F$174)+2,MATCH(2,1/(INDEX('6'' & ADA Tables'!$F$174:$GH$1171,MAX(('6'' & ADA Tables'!$F$174:$GH$1171='Task & Notes Master'!$A10)*ROW('6'' & ADA Tables'!$F$174:$GH$1171))-ROW('6'' & ADA Tables'!$F$174)+1,0)='Task & Notes Master'!$A10))+1-COLUMN('6'' & ADA Tables'!$F$174)+1)

This one is an array formula and must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.

[NBVC]

Maybe you split the formula into 2 separate cells and then concatenate those results in a 3rd cell.

or name each of the tables and use the names instead

or drastically reduce the length of the sheetnames

Btw: Is the first formula I gave not responding or just slow?

[Hoss]

The first formula you gave is not responding.