+ Reply to Thread
Results 1 to 18 of 18

Excel 2007 : Index Match Multiple Criteria Formula

  1. #1
    Registered User
    Join Date
    02-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    8

    Index Match Multiple Criteria Formula

    Hi,

    I have attached a snapshot of current file which is created from Sage, it contains over 16,000 rows...

    Our nominal codes as persheet "Raw Data are in the format of:

    Account Number : Account Cost Centre: Account Department

    Account number is only valid for Blance sheet items i.e 0000 - 9999, and the P&L numbers contain all three of the above for example 4000-100-900, however as you can see from sheet they are located in Column A,C & E.

    Period number (Column G) is 0-12 based on the month in question and Balance TM (Column H) is the value im after, i have created a pivott based on this information but it annoyingly (in my suitation) groups the Account numbers together and wont repeat the number, so i cant see how pivott table can assist.

    I am looking to create monthly accounts based on the Account Number : Account Cost Centre: Account Department bening matched and then referenced to period number the corresponding entry in Row H is produced.

    For example i will in my result sheet have 4000-100-900 in Cell A1 in Cell B1 will be a formula that will look at A1 and break it down into 4000 & 100 & 900 matching it where it appears vertically in Raw Data Sheet in columns A C & E (all three plus period nymber must match) and then give the number in Blance TM column.

    Cell A2000 = 4000-405-800
    Cell B2000 = 265

    Can somebody advise on how to tackle this without using macros?

    Cheers,

    Paddyboy
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index Match Multiple Criteria Formula

    Hi Paddyboy,

    Welcome to the forum.

    Have a look in to sheet 1 of the attached workbook. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Attached Files Attached Files
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    02-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Index Match Multiple Criteria Formula

    Hi Dilipandey,

    As per attached spreadsheet i have couple of questions...

    The formula works for the entry you made and i noticed that in the raw data sheet now that the format of 4000-100-500 are now alligned to the right of the cell, have you formated these in any way, as obvuioulsy the rest of the numbers are alligned to the left of the cell.

    In sheet 1 i have now added other numbers that relate to the raw data but they are not giving correct values is their something in the formula i need to edit to allow for these?

    The fomula you have given is for period 1? How can use fomaula in sheet 2 now to do period 2 etc etc to period 12?

    Additionally how can o get 0010 to display as 0010 in Sheet 1 so that when formula looks for it in raw data, its able to match?

    Thanks for your quick response, much appreciated.....

    Paddyboy
    Attached Files Attached Files

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index Match Multiple Criteria Formula

    You are welcome Paddyboy..

    below are the answers to your queries:-

    Format of 4000-100-500 is still aligned to the left of the cell but in sheet1. If you are referring to Raw Data sheet here, Yes, you are right as they are now aligned to the right of the cell because any number aligned to left of the cell would mean that the number is in text format. Select any cell and press F2 and press Enter -> this will move that cell to the right side i.e., text number will change to number. As you should not be doing this (f2 exercise) for all the cells, you can simply enter 1 in any free cell and copy that, now come to raw data sheet, select the number you want to change format and do paste special values , multiply. Remember numbers like 4000 will move to right side and will remain 4000 but numbers like 0010 will also move to right side but will change to 10.

    Also if you do not do above exercise entirely, refer to row 3 in the attached file where I have picked the result using text numbers only

    As there is no combination exists for 4000-100-500, I have used the combination 4000-201-900 just to check if the number are being picked up correctly. Thanks.

    Feel free to get back in case of any queries.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Index Match Multiple Criteria Formula

    Hi Dilipandey,

    With regards to spreadsheet is their a way round the 0010 being converted to 10, or a way that the formula can recognise this and as per e.g. 4000-201-900 still produce a result, i have tried formating and unformating but keep getting #VALUE, it wont produce a result.

    I have paste special multiply x1 but even though 0010 changed to 10, if i put 10 in sheet 1 it still showns #VALUE.

    Possible solution should i do vlookup on the 0010, that is the numbers where they are of formation 0000 to get the result and put in SHEET 1, but again how can i differinate the lookup to choose the correct period as Vlookup will produce the first result it comes across? and then i could use your fomula to collect data of the format 0000-000-000 as your formula works for this and put in SHEET 2 and finally do a vlookup of both sheets 1 & 2 combining them into one Sheet 3 to get my results?

    Additionally what part of the formula references the Period number i.e Column G, as i want to create a seperate sheet for each period number, so in this case Period 1 should be refernced in Sheet 1 and then Sheet 2 should have period 2 etc etc

    You have been a big help so far, in your posts much appreciated, also it says click on star below if helpfull but their is no star?

    Cheers,

    PaddyboyManagement Accounts 2012 olas.xlsx

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index Match Multiple Criteria Formula

    Hi Paddyboy,

    I suggest you to look into the formula which I have given in row 3 where you do not need to change the format or even don't need to convert numbers from text number to numerics. Kindly go through it and then come up with fresh set of queries. And 'star' is located as black star in bottom left side of every post. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  7. #7
    Registered User
    Join Date
    02-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Index Match Multiple Criteria Formula

    Hi Dilipandey,

    I have not explained my self properly as to what im looking for.

    I was unfortunately not looking for a sum of all the periods. I was looking to create 12 sheets in the the workbook labelled Jan - Dec, then be able to change in the forumala the period number affectig the result.

    For example AN = Account Number ACC= Account Cost Centre AD= Account Department
    AN ACC AD Period Number Balance TM
    4000-201-900, 1, €29,000
    4000-201-900, 2, €350,000
    4000-201-900, 3, €500,000

    I create Sheet (Janurary) wwhere formula looks at A & C & E, and if it sees period 1 gives the result of Column H in relation to that period. i.e €29,000

    I then create a new sheet(February) and it does the same as above but instead i change somehwere in the formula for it to recognise period 2 which gives the result of Colum H in that period. i.e €350,000

    I then create new sheet (March) and it does the same above but i change somehwere in teh formula for it to recognise period 3 whoch gives the result of column H in that period. i.e €500,000

    As per attached you can see that formula also does not appear to recognise following formats: 0010 or 10 or 0010-000-000, the raw data for some items only contains infomration in Column A and no corresponding data in C & E, so in this case i need it to match A only as C & E are blank. Your fomula as per atatched spreadsheet currently prouduces result of #Value for numbers/text in format 0000.

    So for 0010 it should produce result
    -for peirod 1 as €50,000
    -for period 2 as €70,000
    -for period 3 as €10,000

    I hope have explained myself properly this time and apologies for misunderstanding.

    Cheers,

    Patrick


    Management Accounts 2012 olas dilp.xlsx
    Last edited by paddyboy; 03-01-2012 at 05:43 AM.

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index Match Multiple Criteria Formula

    Hi Patrick,

    Below is very clear:-

    So for 0010 it should produce result
    -for peirod 1 as €50,000
    -for period 2 as €70,000
    -for period 3 as €10,000

    as 0010 does not have (AccountCostCentre, AccountDepartment, PeriodNumber)

    but 4000 have some data under above mentioned three headings, so what to do in that case?

    I believe you need to extract data (not sum) basis below 4 criteria :-

    AccountNumber, AccountCostCentre, AccountDepartment, PeriodNumber

    4000, 405, 800, 1 = 2000
    0010,__ ,__, 1 = 50000

    Let me know if I am correct as per above example, so that I can finalize the solution. Thanks.


    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  9. #9
    Registered User
    Join Date
    02-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Index Match Multiple Criteria Formula

    Hi Dilipandey,


    That is correct i am looking for Data to be extracted based on your previous post,
    but i need to be able to alter the formula so for Jan Sheet i can have Period 1 and then February Sheet for period 2 it will extract result, and March Sheet extract for period 3 etc etc..... See example for 0010 below for 3 peiords


    AccountNumber, AccountCostCentre, AccountDepartment, PeriodNumber

    4000, 405, 800, 1 = 2000
    0010,__ ,__, 1 = 50000
    0010,__,___, 2 = 70000
    0010,__,___, 3 = 10000

    Hope this makes sense,

    Patrick

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index Match Multiple Criteria Formula

    Ok.. so we both are on same page.

    And this is what I had referred in my previous post that you need to extract result basis below criteria:-

    AccountNumber, AccountCostCentre, AccountDepartment, PeriodNumber

    whatever is entered under these 4 headings, would be taken as a criteria to extract the result from Raw Data.

    Now my question is do you want to provide criteria in 4 columns as above or do you want to provide it as a single value in a cell like below :-

    4000-405-800-1

    ?

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  11. #11
    Registered User
    Join Date
    02-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Index Match Multiple Criteria Formula

    Management Accounts 2012 olas dilp.xlsxHi Dilipandey,

    I dont fully understand your point....

    It's important that yes 4 columns are seached and when it matches, what i have in janurary sheet, february sheet, March sheet etc etc it extarcts the correct Data and places it in the cell where formula is.

    If in Jan Sheet a1 i have 0010, the formula in A1 must go to raw data sheet and look for 0010, then it looks for period 1 and finally if it matches it gives the result in from the balance tm column €1,000.

    If in Feb a1 i have 0010, then it must do the above but i must be able to change the criteria that its now looking for period 2 and will give the result in the Blance tm column assoicated with period 2., €2,000

    As you can see 0010 only has 2 vlaues to match in the Raw data sheet i.e 0010 and the period number however 4000-405-800 has 4 vlaues to match the 4000 the 405 the 800 and the period number before it can yield a result from Period 1.

    So maybe 0010 must also have 4 values to match in that if it finds 0010 then the other 2 values which are are blank it can still yeild a result? i.e as it has blank cells in Column C & E...

    I hope this makes sense? Please see attached spreadsheet where i have updated the value i would expect to see.

    Patrick
    Last edited by paddyboy; 03-05-2012 at 05:32 AM.

  12. #12
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index Match Multiple Criteria Formula

    Hi Patrick,

    You were already clear to me in your previous post, but what I wanted to know is how you are going to provide the criterias, see the attachment and let me know which one? Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    03-05-2012
    Location
    Iran
    MS-Off Ver
    Excel 2010-Excel 2007
    Posts
    4

    Question Please help me

    Hi dear friends
    I have a problem . please help me.
    There is a column with different names . I want a list validation in C2 cell bay this condition :*
    When type a letter in C1, my list in C2 contain every name in column A that have C1 letter .
    Thanks million .

  14. #14
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index Match Multiple Criteria Formula

    Hi ladan.. .

    Welcome to the forum.

    Please do not cross post into an existing thread, rather start a new thread. Thanks.

    Regards,
    DILIPandey

    <click on below star if his helps>

  15. #15
    Registered User
    Join Date
    02-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Index Match Multiple Criteria Formula

    Hi DILIPandey,

    Im really not sure what you mean by criteria, as you know raw data sheet the items im looking for are in Column A & C & E & G, when these match they will give the corresponding result from Column I.

    in Jan sheet if i understand what you mean then i will have A1 4000-201-900 and in B1 1 (i.e. period number) and C1 will contain the formula which will give me result from colum I?

    In feb sheet i will have A1 4000-201-900 and B1 2 (i.e. Period number) and C1 will contain formula which will give me result from Column I?

    If i understand the above then please use option 1 as in your example.

    Aplogies for my poor understanding,

    Patrick

  16. #16
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index Match Multiple Criteria Formula

    Hi Patrick,

    Considering each and everything, I have developed the required formula and cross checked that as well and after satisfying myself, I am attaching the same here for your review. Thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    02-27-2012
    Location
    Dublin
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Index Match Multiple Criteria Formula

    Hi Dilip,

    Apologies for delay in reply, thank you so much for all your help.

    That is brilliant what you have done, it has also made sense to me now how you constructed the formula, i can now apply to other spreadhseets i have.

    Thanks again for your patience and effort in completeing this.

    Very much appreciated.

    Patrick

  18. #18
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index Match Multiple Criteria Formula

    Hey Patrick... you are welcome friend.

    and just to share, I keep on learning things because of my patience and efforts

    Cheers

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1