+ Reply to Thread
Results 1 to 5 of 5

multiple criteria for index and match against multiple worksheets

  1. 11-23-2007, 01:41 PM #1
    reggie1000
    reggie1000 is offline
    Forum Contributor
    Join Date
    11-23-2007
    Location
    Suffolk, UK
    Posts
    298

    multiple criteria for index and match against multiple worksheets

    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
    Reply With QuoteRegister To Reply
  2. 11-23-2007, 03:38 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    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: 
    Please Login or Register  to view this content.
    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:

    Please Login or Register  to view this content.
    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
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 11-27-2007, 07:53 AM #3
    reggie1000
    reggie1000 is offline
    Forum Contributor
    Join Date
    11-23-2007
    Location
    Suffolk, UK
    Posts
    298
    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
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 11-27-2007, 09:22 AM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    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:

    Please Login or Register  to view this content.
    Don't forget to confirm with CSE key combo and then copy down.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 11-27-2007, 10:41 AM #5
    reggie1000
    reggie1000 is offline
    Forum Contributor
    Join Date
    11-23-2007
    Location
    Suffolk, UK
    Posts
    298
    Thanks NBVC,

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

    reg
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1