+ Reply to Thread
Results 1 to 10 of 10

Index Match Function across different worksheets

  1. 04-27-2015, 09:00 AM #1
    Shay_mt
    Shay_mt is offline
    Registered User
    Join Date
    04-05-2015
    Location
    IL
    MS-Off Ver
    2013
    Posts
    4

    Index Match Function across different worksheets

    Hi
    I'm not very familiar with Excel
    I am able to get along with Index Match on the same sheet but Index Match Function across different worksheets gives me a real headache and I tried for a long time with no success
    Please help

    I have 12 sheets every sheet present a month
    Every sheet have the following table (all sheets have the same table)
    Column A for Date
    Column B for Name
    Column C for Quantity

    monthly sheet.JPG

    Also I have summary sheet that present the higher quantity using MAX formula that crossing on all the sheets

    summary sheet.JPG

    I would like to get the "Name" in Column B of the summary sheet

    I tried to use Index Match formula with no luck

    Thanks in advance

    Shay
    Reply With QuoteRegister To Reply
  2. 04-27-2015, 09:05 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Index Match Function across different worksheets

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    http://www.msofficeforums.com/excel/...html#post82073
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 04-27-2015, 09:05 AM #3
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Index Match Function across different worksheets

    pls attach a sample excel file
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  4. 04-27-2015, 09:25 AM #4
    Shay_mt
    Shay_mt is offline
    Registered User
    Join Date
    04-05-2015
    Location
    IL
    MS-Off Ver
    2013
    Posts
    4

    Re: Index Match Function across different worksheets

    Hi Pepe Le Mokko

    Sorry I didn't read the rule, this will not happen gain!!

    Hi nflsales

    Please check the attach file

    Example.xlsx

    Thanks in Advance and sorry again for the multiple threads

    Shay
    Reply With QuoteRegister To Reply
  5. 04-27-2015, 09:54 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Index Match Function across different worksheets

    Can I re-order the data, putting quantity at the left colum in the monthly sheets?
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  6. 04-27-2015, 10:16 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Index Match Function across different worksheets

    Never mind. I got it working. What you asked for is not that easy to achieve. I created a "Named Range" containing all of the months of the year (CTRL F3 to see it). The list is on a separate sheet that can be hidden.

    The only way that I know to do this (apart from using a VERY long formula - mad for 12 sheets, assuming you have one per month)) is to use VLOOKUP. However, it only looks things up going from Left to Right. You want to look up a date and a name that are to the left of the quantity - so that added a layer of complexity. Finally, it's an Array Formula.



    They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 05-01-2015, 09:10 AM #7
    Shay_mt
    Shay_mt is offline
    Registered User
    Join Date
    04-05-2015
    Location
    IL
    MS-Off Ver
    2013
    Posts
    4

    Re: Index Match Function across different worksheets

    Hi Glenn

    Thanks for your response, Unfortunately Vlookup not good for me because I need it to looking on thing going from Right to Left also I have a lot of multiple values most of them are cell that starting with 0:00 and it seems that Vlookup did not handle it so well.

    I will continue to find a solution and I will post here when I will find one
    By the way I do not mind with that VERY long mad formula, if you can post the code here 2 sheets I will try to complete it

    Thanks any way

    Shay
    Reply With QuoteRegister To Reply
  8. 05-01-2015, 09:17 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Index Match Function across different worksheets

    I may not have expressed myself clearly in the text of my previous post, but the title of my atachment might have been a clue. The VLOOKUP formula, as structured, DOES run right to left. Try it.

    If your one line of data is not representative, then please post a sheet that DOES contain representative data.
    Reply With QuoteRegister To Reply
  9. 05-07-2015, 09:13 AM #9
    Shay_mt
    Shay_mt is offline
    Registered User
    Join Date
    04-05-2015
    Location
    IL
    MS-Off Ver
    2013
    Posts
    4

    Re: Index Match Function across different worksheets

    Hן Guys

    I found a solution , it's really long code but works great

    =IFERROR(INDEX(jan!A2:C5,MATCH(C2,jan!C2:C11,0),2),IFERROR(INDEX(feb!A2:C5,MATCH(C2,feb!C2:C11,0),2),IFERROR(INDEX(mar!A2:C5,MATCH(C2,mar!C2:C11,0),2),IFERROR(INDEX(apr!A2:C5,MATCH(C2,apr!C2:C11,0),2),INDEX(may!A2:C5,MATCH(C2,may!C2:C11,0),2)))))

    Thanks anyway

    Shay
    Reply With QuoteRegister To Reply
  10. 05-07-2015, 10:57 PM #10
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Index Match Function across different worksheets

    Another way. With a lookup table of sheet names in H1:H5 of 'summary'...for the MAX this array-entered formula in C2 of 'summary'.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    Then in A2 this array-entered formula.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    and in B2 this array-entered formula.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    The sheet is attached.

    Results are:

    Row\Col
    A
    B
    C
    1
    		 Date Name Quantity
    2
    15-Jan-14
    		 Sara
    13200
    Attached Files Attached Files
    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)

Similar Threads

  1. [SOLVED] INDEX/MATCH across several worksheets
    By hennakao in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-25-2016, 08:07 AM
  2. INDEX and MATCH across two worksheets
    By murleyj in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 07-13-2014, 01:41 PM
  3. Index and Match from different worksheets
    By djbcktt in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2013, 03:27 PM
  4. Lookup or Index(match) function not in ascending order and not an exact match
    By Tobias1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  5. Need to use MATCH and INDEX over 2 worksheets
    By khedger in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-11-2012, 09:55 AM

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