+ Reply to Thread
Results 1 to 9 of 9

Index Match Help needed

  1. #1
    Registered User
    Join Date
    06-23-2015
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    60

    Index Match Help needed

    Hi Dears,

    Hope you are doing well,

    May I ask you some help to adapt my Index/match formula please ?

    I am trying to retreave some data's from the sheet pivot FCST, based on 6 different criterias, Cust group/Brand,Format,bottle type, Month and year,

    But only for the months 10 to 12

    Thanks in advance,
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Index Match Help needed

    In that excel, customer group how you match?

  3. #3
    Registered User
    Join Date
    06-23-2015
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Index Match Help needed

    I am sorry,

    I pasted the table in the wrong way (missing a column), here-below you will find the correct one
    Attached Files Attached Files

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Index Match Help needed

    In O5, try this:

    =SUMIFS('Pivot FCST'!E:E,'Pivot FCST'!$A:$A,$B5,'Pivot FCST'!$B:$B,$C5,'Pivot FCST'!$C:$C,$D5,'Pivot FCST'!$D:$D,$E5)

    Copy to the right and down.

  5. #5
    Registered User
    Join Date
    06-23-2015
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Index Match Help needed

    Hi,

    Would it be possible to have this formula more dynamic ? taking into account Month number and Year number?

    I have tried to add it in your formula but i have a value error return,

    Thanks in advance !

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Index Match Help needed

    Based on how your data is set up in your example, it doesn't need to look at month or year number.

    The INDEX portion of the function moves over one column for every column that the formula is dragged over. There is no need to complicate it.

    If this formula isn't working as you expect on your actual spreadsheet, perhaps you should create a small representative sample of your data along with the desired result/s of the formula/s.

  7. #7
    Registered User
    Join Date
    06-23-2015
    Location
    Brussels, Belgium
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Index Match Help needed

    Hi,

    I agree with you,

    For example, for the time being you have the month october which is in column E:E in the sheet pivot FCST,

    But If i am updating the file in the future, let's say November, October won't be present in the Pivot FCST sheet anymore and replaced by the month november,
    That means that the column November in my sheet overview will return a value from month December in the Pivot FCST sheet (because it is not dynamic enough)

    hope it is clear let me know,

    Thanks in advance

    So in my overview sheet, the formula in month november will return a value from month

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Index Match Help needed

    In O5, try this:

    =SUMPRODUCT(('Pivot FCST'!$A$5:$A$695=$B5)*('Pivot FCST'!$B$5:$B$695=$C5)*('Pivot FCST'!$C$5:$C$695=$D5)*('Pivot FCST'!$D$5:$D$695=$E5)*('Pivot FCST'!$E$1:$V$1=O$2)*('Pivot FCST'!$E$2:$V$2=$D$1)*('Pivot FCST'!$E$5:$V$695))

    Note that the calculations are set to manual in the workbook that you shared.

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Index Match Help needed

    If you want to use INDEX MATCH, you can use this:

    O5 =INDEX('Pivot FCST'!$E$5:$V$900,MATCH(1,('Pivot FCST'!$A$5:$A$900=$B5)*('Pivot FCST'!$B$5:$B$900=$C5)*('Pivot FCST'!$C$5:$C$900=$D5)*('Pivot FCST'!$D$5:$D$900=$E5),0),MATCH(1,('Pivot FCST'!$E$1:$V$1=O$2)*('Pivot FCST'!$E$2:$V$2=$D$1),0)) Ctrl Shift Enter

    or this (non-array version):

    O5 =INDEX('Pivot FCST'!$E$5:$V$900,MATCH(1,INDEX(('Pivot FCST'!$A$5:$A$900=$B5)*('Pivot FCST'!$B$5:$B$900=$C5)*('Pivot FCST'!$C$5:$C$900=$D5)*('Pivot FCST'!$D$5:$D$900=$E5),0),0),MATCH(1,INDEX(('Pivot FCST'!$E$1:$V$1=O$2)*('Pivot FCST'!$E$2:$V$2=$D$1),0),0))

+ 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. [SOLVED] Match and index needed (I think)
    By Agnese in forum Excel General
    Replies: 2
    Last Post: 03-15-2016, 07:50 AM
  2. Index and Match help needed
    By karstens in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-18-2015, 01:34 PM
  3. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  4. Sum Index Match help needed
    By vhache in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2014, 09:01 AM
  5. Index Match help needed
    By SKIDDERWOLF in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-14-2013, 04:19 AM
  6. Help needed with INDEX MATCH...
    By Nerf Herder in forum Excel General
    Replies: 4
    Last Post: 06-27-2012, 04:40 AM
  7. Index and Match Help Needed
    By carl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-26-2005, 05:05 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