+ Reply to Thread
Results 1 to 11 of 11

Match/Index over multiple tabs

  1. #1
    Registered User
    Join Date
    05-26-2021
    Location
    Canada
    MS-Off Ver
    Microsoft 365
    Posts
    24

    Match/Index over multiple tabs

    Hi,

    I am trying to do a match/index over multiple tabs but it will not work.

    My formula is located in the 'Design' tab in the F Column, see the attached file

    =IF(ISNA(MATCH($D2,'F1'!$A$2:$A$200,0)),0,INDEX('F1'!$G$2:$G$200,MATCH($D2,'F1'!$A$2:$A$200,0)))

    This is the formula that works for one tab, the "F1" tab in this case.

    So basically, it compares the value of the D column in the Design tab with the value in the F1 tab at a certain column and return the price if the cells matches. (Column F = Prix, which is "price in french")

    Does anyone know how to make it work over multiple tabs, say 40 tabs ?

    Best regards,
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Match/Index over multiple tabs

    Question: what differentiates F1 from F40?

  3. #3
    Registered User
    Join Date
    05-26-2021
    Location
    Canada
    MS-Off Ver
    Microsoft 365
    Posts
    24

    Re: Match/Index over multiple tabs

    Quote Originally Posted by JohnTopley View Post
    Question: what differentiates F1 from F40?
    Each tab would represent a different order.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Match/Index over multiple tabs

    I would recommend you have a single table with a column representing what is currently the tab name (F1, F2 etc).
    Last edited by JohnTopley; 05-26-2021 at 04:29 PM.

  5. #5
    Registered User
    Join Date
    05-26-2021
    Location
    Canada
    MS-Off Ver
    Microsoft 365
    Posts
    24

    Re: Match/Index over multiple tabs

    Quote Originally Posted by JohnTopley View Post
    I would recommend you have a single table with a column representing what is currently the tab name (F1, F2 etc).
    This would not work as I would like to be able to have twice the same row but with a different quantity in the same sheet and it doesnt add up correctly if I have more than one row with the same name.

    Any other ideas ?

    Thanks,

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Match/Index over multiple tabs

    I don't understand your logic: you could have 20 rows with "Bananas" with an "Order Number" for each row. You can summarise by Order or any other parameter.

  7. #7
    Registered User
    Join Date
    05-26-2021
    Location
    Canada
    MS-Off Ver
    Microsoft 365
    Posts
    24

    Re: Match/Index over multiple tabs

    Quote Originally Posted by JohnTopley View Post
    I don't understand your logic: you could have 20 rows with "Bananas" with an "Order Number" for each row. You can summarise by Order or any other parameter.
    I tried to summarize by order but the right amount will not update correctly in E column of the tab "Design".

    As you can see in the attached file, see the F1 tab for example.

    I might be misunderstanding what you are proposing, sorry if this is the case.

    Best regards,
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Match/Index over multiple tabs

    To SUM .

    in E2.

    =SUMIFS('F1'!C:C,'F1'!B:B,Design!D2)

    Copy down

  9. #9
    Registered User
    Join Date
    05-26-2021
    Location
    Canada
    MS-Off Ver
    Microsoft 365
    Posts
    24

    Re: Match/Index over multiple tabs

    Quote Originally Posted by JohnTopley View Post
    To SUM .

    in E2.

    =SUMIFS('F1'!C:C,'F1'!B:B,Design!D2)

    Copy down
    Seems to be working, thanks for your help and have a nice day.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    Re: Match/Index over multiple tabs

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,085

    Re: Match/Index over multiple tabs

    Here is the macro code to fill formulas given by JohnTopely , with slight modification ,in range G2 to last row Last column. Filling manually is a difficult job.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Can't get INDEX/MATCH to work across multiple tabs
    By bpigott in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-30-2019, 03:05 PM
  2. Index Match across multiple tabs
    By markman235 in forum Excel General
    Replies: 9
    Last Post: 12-28-2018, 10:07 PM
  3. Index/Match over multiple tabs
    By traggs25 in forum Excel General
    Replies: 8
    Last Post: 06-14-2018, 04:32 PM
  4. Index Match Max Multiple Tabs
    By AverageJoe2015 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-19-2017, 04:24 PM
  5. Index/Match with across multiple tabs
    By StefaniaLa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2016, 06:19 AM
  6. [SOLVED] Multiple Match/Index for Individual Tabs
    By hartjohnson in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-13-2016, 06:47 PM
  7. Index Match Across Multiple Tabs?
    By ratherbeknitting in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2015, 11:46 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1