multiple criteria for index and match against multiple worksheets

[reggie1000]

Hello,

i have a problem, i have a mileage matrix for towns this is spread across 8 worksheets, there are approx 500,000 cells, i need a formula in one cell only to index and match against 2 criteria (row and column) across all of the sheets, the result will be unique to each of the criteria, i can index and match against one sheet but incorporating more than one sheet is not working, does any one have any ideas???

thanks reg

[NBVC]

Try this procedure:

In the sheet you want to enter the formula in, list the names of the sheets in any 8 adjacent cells. Go to Insert|Name|Define and enter "TabNames" without the quotes.. in the refers to box, enter the range that you entered your sheet names. Click Add.

In the names field enter the name "MatchTrue" and in the refers to field enter formula:

=MATCH(TRUE,COUNTIF(INDIRECT("'"&TabNames&"'!C1:C13"),Sheet1!A1)>0,0)

where C1:C13 is the largest vertical range of the 1st column in each of the 8 lookup sheets...adjust as necessary.

Click Add and click Ok.

Now in the cell you want your results sheet enter formula:

=INDEX(INDIRECT("'"&INDEX(tabnames,MatchTrue)&"'!C1:H13"),MATCH(A1,INDIRECT("'"&INDEX(tabnames,MatchTrue)&"'!C1:C13"),0),MATCH(B1,INDIRECT("'"&INDEX(tabnames,MatchTrue)&"'!C1:H1"),0))

Where C1:H13 is the table range in each of the sheets. C1:C13 is the largest range of the largest first column range in each sheet and C1:H1 is the largest first row range in each sheet.

adjust all ranges to suit.

Note: This is an array formula...after you fix it up, confirm it with CTRL+SHIFT+ENTER not just ENTER. You'll see { } brackets appear around the formula

[reggie1000]

Thanks for the reply NBVC,

i have tried to enter the formula in, but having problems with the named ranges for "MatchTrue", i have attached an .xls with my original formulas and yours, hope you can help out again.

i have had to strip out a lot of data to get under the 100k, hopefully you can understand the principle of the sheets.

cheers reg

[NBVC]

Ok then...

Firstly, I didn't know you had the same 1st column in each sheet and only the 1st row headers changed...that changes the formula slightly...

Secondly, not sure why the MatchTrue named range doesn't give correct result..so I say forget about it and I re-incorporated the MatchTrue formula into the main spreadsheet formula....see attached..

Formula in H5 is now:

=INDEX(INDIRECT("'"&INDEX(TabNames,MATCH(TRUE,COUNTIF(INDIRECT("'"&TabNames&"'!$A$1:$iu$1"),DATA!D5)>0,0))&"'!$A$1:$iu$500"),MATCH($C5,MILEAGE1!$A$1:$A$500,0),MATCH($D5,INDIRECT("'"&INDEX(TabNames,MATCH(TRUE,COUNTIF(INDIRECT("'"&TabNames&"'!$A$1:$iu$1"),DATA!D5)>0,0))&"'!$a$1:$iu$1"),0))

Don't forget to confirm with CSE key combo and then copy down.

[reggie1000]

Thanks NBVC,

its works like a dream, thanks for all your help.

reg