+ Reply to Thread
Results 1 to 21 of 21

Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

  1. #1
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hello,

    After many hours of surfing the web to no avail I decided to call for help here. I am good with excel but when it comes to specific functions and correct syntax I have problems.

    I am trying to to have my destination cell show me the price for a specific item based on 3 criteria. 1st one is in the column of the kind of item. 2nd criteria is what type of material it is. and 3rd is what length the item is.

    My "material type" is in one row with 4 different material types. My lengths for the item are in the subsequent row below. So for every "material type" there are 3 different lengths. So simply 3 columns for sizes inside the material type column. And ofcourse the item list itself in the left most column. I cannot get my formula to work to recognize all three criteria to populate the destination ccell with the relative price. I understand how to do index array with 2 match properties, but with 3 (2 rows for criteria) is not working. I have attached the sheet were my data is. On a different sheet I have 3 different cells to match the criteria. One is for the item, one is for the type of material and one is for the length of the item. My material type and item are drop down lists from the same data sheet. My length though is just formatted to show the inch sign. Otherwise I put in the data myself. Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hi,

    Does the attached help

    Pick from the 3 drop downs in B3:B5 on sheet2 and the result will be in C2
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hello again. Figured it out. It was as simple as putting the & sign in the criteria to have it search both. However I am at a different problem now. My third criteria which gives me the length of the item was not a drop down. I had to change it to a drop down to have the formula work. However now my other simple formula which calculates board footage is now giving me a value error since it does not recognize the length from a drop down, as opposed to just typing it in. So my question is how do I get that formula which calculates my board footage, to recognize the numbers in the drop down to make the calculation. Thank you.
    Last edited by volchik696; 01-07-2014 at 04:22 PM.

  4. #4
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Thank you very much for the prompt reply. Yes your attachment was helpful. I however figured it out by playing around with the syntax.

    =INDEX(Sheet1!$C$4:$C$6,MATCH(Material!$D$11&Material!$K$11&Material!$L$11,Sheet1!$B$4:$B$6&Sheet1!$C$2&Sheet1!$A$4,0))

    Thats is my formula in the destination cell that pulls the correct price for the 3 criteria given.

    However I came at a different issue. Now that cell that is criteria 3 for the length of the item, is a drop down list for the lengths given. However a different cell uses that cell to calculate the board footage for me. And it will not recognize the number from a drop down list. Gives me a error value message.

    How can I make that cell recognize the numbers in the drop down? or is that not possible? Thank you.

  5. #5
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    I've attached a sample of the error it gives me.
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hi,

    Your difficulties, (or complications) arise because you are choosing to add the inches "" character to your numeric lengths which means that they can no longer be used as numbers for arithmetic purposes.

    You have a choice.
    Either
    1. Use numbers only in L2:M2, or
    2. Adjust your G11 formula so that it strips out the "" character. i.e.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hello again. Thanks for that left function. Did not know that existed read up on it and will use it in the future. I am back to my original issue with the 3 parameters to match arrays. I think the reason it worked before is because I tested it on just one item with the 3 lengths. Now again it gives me a value error message in the cell. I have attached the workbook that I am trying to fix. In the "data sheet" I put 2 tables to show you guys to see which one you think is better to work with. I worked with the 1st one up top.

    Richard the prior reply you sent was helpful thank you but it does not work for me because those cells that you have referenced for the table that show the numbers, in my workbook they need to show what I am choosing. I have nowhere to put another cell with the matching functions. That is why I am trying to create one function in that particular cell for all the 3 array parameters I need. So if you can take a look and suggest how to go about this I would really appreciate it.

    P.S.
    On a side note, is there a way to get rid of blanks in the drop down list due to merged cells? I know I can create a separate table for that and that's fine. But I like my stuff neat and organized, so if there is a way to merge cells and not have blanks when you reference them to a drop down list that would be great.

    Again thanks so much for everybody's help.
    Attached Files Attached Files

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hi,

    Perhaps something like:

    =INDEX(Data!B4:S87,MATCH(M7,Data!A4:A87,0),MATCH(Sheet1!L7,Data!B2:S2,0)+LOOKUP(E7,{"48","54","60"},{0,1,2}))

    Or even:

    =INDEX(Data!B4:S87,MATCH(M7,Data!A4:A87,0),MATCH(Sheet1!L7,Data!B2:S2,0)+MATCH(E7,{"48","54","60"}&"""",0)-1)

    Regards
    Last edited by XOR LX; 01-08-2014 at 11:13 AM.
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  9. #9
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Worked perfectly. A thousand times thank you. Been trying to figure it out for the last 2 days. I wish I could understand the syntax. I understand the first par but the end for lookup function I don't get how it works even though it works heh. If you can recommend any good reading material or videos on it I would greatly appreciate it. I would begin to ask less stupid questions.

    If you can possibly help me in figuring out how to get rid of blank spaces in my dropdown list due to merged cells that would be great. I know I can create a separate list but if I can have the less raw data and the more organized I rather force it with functions instead of creating more tables just for drop downs.

    Also how can I make my error values just show up blank or as zero's when theres no input information. To explain, if I have 3 cells saying 5, 10, 15. And my fourth cell adds them up and gives me an average. If theres no information in one of the first 3 cells, the fourth cell will give me an error message. How can I get rid of that error message so its just blank or zero up until I put in the required information for that cell to make the calculation. Thank you very much.

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Quote Originally Posted by volchik696 View Post
    Richard the prior reply you sent was helpful thank you but it does not work for me because those cells that you have referenced for the table that show the numbers, in my workbook they need to show what I am choosing. I have nowhere to put another cell with the matching functions. That is why I am trying to create one function in that particular cell for all the 3 array parameters I need. So if you can take a look and suggest how to go about this I would really appreciate it.

    P.S.
    On a side note, is there a way to get rid of blanks in the drop down list due to merged cells? I know I can create a separate table for that and that's fine. But I like my stuff neat and organized, so if there is a way to merge cells and not have blanks when you reference them to a drop down list that would be great.

    Again thanks so much for everybody's help.
    Hi,
    Those cells were only intended as helper cells. I'm surprised you have nowhere else to put them. They could be out of the way anywhere, a hidden sheet if you prefer. I did this since it's easier to read formulae when the constituent parts of a formula are broken down with helper cells/

    If however you want to incorporate the helper cells in a single formula for N7 then

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You should try and avoid merged cells at all costs. Somewhere down the line they will trip you up, particularly if you get round to using macros. Most of us avoid them like the plague they are much too troublesome. Usually for a horizontal range a normal fomat to 'Center Across Selection' achieves the same thing without needing to merge cells.

  11. #11
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Yes it does seem like merged cells definitely mess with tables functions, etc. Thank you for everybody's help. It absolutely helped me move forward. If you have recommendations on good books or videos to become proficient with Excel, would be greatly appreciated. Thank you and take care.

  12. #12
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hello again people. I again am having issues with my functions. If you see the attached book, I am trying to get my "cost per" cell to give me the price reflecting in the table on the right. It has to match up 3 drop down list cells. The "component" , "style" , and "material" need to be all matched up to get the corresponding price.

    However as you see it gives me an error. I have used the same type of formula before but maybe my problem is that the first one has only 5 lookups, while this one has 12. Maybe it doesnt work with such an amount. Also my dropdown component list has huge gaps due to merged cells. How do I remedy that also? Make a different table without merging?

    So main point is how to properly syntax the index and match functions, also maybe lookup, to get the price corresponding to the 3 drop down menus.

    Thank you for everybody's help and have a great day.

    P.S.
    Maybe my data table is bad. Maybe there is another way to make that table that would take less space and would be easier to work with in functions. Thanks again.
    Attached Files Attached Files

  13. #13
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Quote Originally Posted by volchik696 View Post
    Hello again people. I again am having issues with my functions. If you see the attached book, I am trying to get my "cost per" cell to give me the price reflecting in the table on the right. It has to match up 3 drop down list cells. The "component" , "style" , and "material" need to be all matched up to get the corresponding price.

    However as you see it gives me an error. I have used the same type of formula before but maybe my problem is that the first one has only 5 lookups, while this one has 12. Maybe it doesnt work with such an amount. Also my dropdown component list has huge gaps due to merged cells. How do I remedy that also? Make a different table without merging?

    So main point is how to properly syntax the index and match functions, also maybe lookup, to get the price corresponding to the 3 drop down menus.

    Thank you for everybody's help and have a great day.

    P.S.
    Maybe my data table is bad. Maybe there is another way to make that table that would take less space and would be easier to work with in functions. Thanks again.
    Yes indeed you need to simplify that lookup table by ensuring every cell in column O contains the component name, and in another helper cell concatenate cells P&O. e.g. =P3&O3 to give a result CopedEnd6010

    Then your helper column becomes unique and you can use this as the second MATCH() element in an INDEX() function to capture the Row reference.
    Without simplifying in this way you are stuck with the rather messy

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    in D2 copied down

  14. #14
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Richard thank you for your reply. Would it be possible for you to give a small example of the index and match functions with the contactenated cells. I have tried the messy formula and it is not working for me. I am in the process of redesigning the table for that purpose. The reason I ask for a sample is because I know I will run into problems when putting the formula together because I basically just reverse engineer your samples and make them work to my criteria. I will upload soon a sample of what I've done. Much appreciated.

  15. #15
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hello again everybody. This has become my favorite site for help. I was hoping for some help from here. I've attached what I am trying to do. If you look at cell C6, the formula in there gives me the diagonal length for a staircase. Simply uses the pythagorean theorem. The formula squares the numbers in cells C4 and C5, then multiply's those by the quantity of each item giving the total length for the legs (A^2, B^2), and finishes off by square rooting the result and dividing by 12 giving me an answer in feet from working in inches.

    2 Issues that I have here. Issue number 1 is that I put in the if(iserror function to hide error values when the input cells are empty as shown in the second table down, however it still gives me a 0' when all the data cells are empty. I'd appreciate if someone can show me what's wrong and how to get an empty cell when there's no data instead of a 0'.

    Issue number 2 is sometimes I calculate stairs that have no risers (vertical part). I will always put in the quantity of risers but the width would be open space, so no numbers as shown in the third table down. Is there a way for me to still get a calculation that will give me the correct diagonal length of the staircase whether I have data in the riser width or not. If you notice how it gives me 9 13/16' in cell C28, when it should be the same as in table 1 because the dimensions are the same in terms of geometry, just that the vertical part of each step would be open and see through. The rise itself still has a dimension. Would really love someone's help on this.

    Another option for issue 2 maybe is to put in the vertical step height even if its empty space, but then exclude the board footage that will be calculated for me for all other parts minus the vertical steps which are empty space. I now I can just use sum function and exclude that cell but the problem is, is that next time I open a template for a job with the vertical steps I would have to change that sum formula to accommodate the change. How do I make that change automatic is what i am trying to do.

    Please let me know if this is too scripted and i will try to elaborate as simple as i can. Thank you everybody and have a great day.
    Attached Files Attached Files

  16. #16
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hi

    A more appropriate formula in C6 might be
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Difficult to see how you can get an answer for C28 since you are relying on pythagoras and need to know two sides of the triangle. One way or another you are going to need to enter a dimension somewhere and use that in the SQRT formula. You could detect the presence of a number in this other dimension cell and use it in an IF test to modify the C5 bit of the formula.

  17. #17
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    You're absolutely right Richard. There's my dilemma that my formula relies on the leg dimensions for the hypotenuse and it takes 2 to work.

    I can just exclude the riser cell from the sum formula in G12, just like I excluded G9 since that number is in linear footage while the rest is in board footage.

    However the problem is, that if I need to calculate a job were the risers are present, I would need to manually change the sum formula in G12 to include the risers calculated board footage. And I'm pretty sure there is no way automate that aspect in respect to the job.

    I guess I'm just going to keep in mind of that little discrepancy when doing my calculations.

    I want it all to be as automatic as possible for quick and fast price outs so that's why I inquired about this.

    But thank you very much again you are very helpful.

    P.S.
    If only there was a different formula available to calculate this
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hey Richard I uploaded a book to show you whats going on. The countblank function is definitely doing something that is throwing off the numbers. If you look at the first table in cell D5 has the blankcount formula.

    The second table just has the calculation is cell D23. All the data fields are the same however it gives different numbers. The number in the second table is actually correct pertaining to the data, while the number in the first table in Cell D5 is very much off.

    Plus if you play around with the quantity numbers, the results get even more irrational and are wrong.

    For instance if we have 11 treads and 12 risers like in table 3, the result is 3 feet or so, where in reality it needs to be closer to 12'. Would love your input on this. Thank you and have a great day.

    P.S.
    Here is a link that calculates automatically the diagonal length of the stair to give you an idea of how it works. http://www.blocklayer.com/Stairs/StairsEng.aspx Just choose stairs imperial up top and it calculates automatically.
    Attached Files Attached Files
    Last edited by volchik696; 01-16-2014 at 06:21 PM. Reason: Please disregard the first book 1. The second book 1 is the relevant one (31.5 kb)

  19. #19
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Hi,

    The difference between D5 & D23 is nothing to do with the Countblank.

    In D5 you are taking the SQRT of ((E3^2*C3)+(E4^2*C4))/12)
    In D23 you are taking the SQRT of (E21^2*C21)+(E22^2*C22)) and then dividing the answer by 12. Subtle but important difference.

    To answer an earlier question, if you leave out the Risers number in C22 because this is an open staircase, you'll still need to hold the number somewhere else in order for the calculation to be correct. So suppose you put the number in say A18 then the formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    will continue to calculate correctly

  20. #20
    Registered User
    Join Date
    01-07-2014
    Location
    Brookly, New york
    MS-Off Ver
    Excel 2011
    Posts
    21

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Good Morning Richard,

    You're right. I see my mistake. I got it to work. with the countblank function too. The trick was for me to use the pythogorean theorem for a single step, calculate that part, and then multiply the answer by the number of steps. Now it works correctly. Thank you for your help.

  21. #21
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Array index with 3 way match criteria of 2 rows and 1 column cant get to work!

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. [SOLVED] Index Match in an Array 2 Criteria
    By Dendrinos2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2013, 08:24 PM
  3. Replies: 6
    Last Post: 04-12-2013, 05:50 AM
  4. Replies: 3
    Last Post: 03-31-2013, 01:53 PM
  5. [SOLVED] VBA for creating Index-Match every 2 rows where INDEX refer to a different column per row
    By bisaya789 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2012, 03:46 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1