+ Reply to Thread
Results 1 to 9 of 9

Dynamic search; using column # result to determine column used for column/row match.

  1. #1
    Registered User
    Join Date
    09-11-2014
    Location
    Gloversville, NY
    MS-Off Ver
    MS Excel 2007
    Posts
    5

    Dynamic search; using column # result to determine column used for column/row match.

    Hello,

    I'm just becoming familiar with INDEX and MATCH functions and haven't been able to figure the right combination for my circumstances.

    I am building a budget model. Monthly, I'll be importing a given months expenses from Quicken. Since each month will have a different set of row descriptions (one month say rent and utilities, the next month maybe rent and garbage), I need to include both the description column, and amount column each month. Creating a standard list of descriptions that line up is not an option since I will introduce new descriptions occasionally and since Quicken does not have an "include zero items" feature (so my descriptions within rows rarely line up).

    A B C D E F
    1 1/31/14 2/28/14 3/31/14
    2 Rent $100 Rent $102 Rent $103
    3 Utilities $50 Garbage $10 Repairs $75

    Then, in another part of the sheet (or possibly another sheet), I need to create a formula that will find the matching date, then match the description under that date (Rent, Utilities, etc.), and finally return the associated amount to that month.

    A B C D
    7 1/31/14 2/28/14 3/31/14
    8 Garbage (solve) (solve) (solve)
    9 Rent (solve) (solve) (solve)
    10 Repairs (solve) (solve) (solve)
    11 Utilities (solve) (solve) (solve)
    12 Water (solve) (solve) (solve)

    I tried a VLOOKUP/Match, but once the match found say Utilities in A3 and gave $50 correctly in my target B11, subsequent columns for Utilities remained stuck on Row 3 and incorrectly returned D3 (Garbage) $10 into target C11 Utilities and F3 (Repairs) $75 into target D11 Utilities. After doing some reading, I realize the limitations of VLOOKUP. I also tried an INDEX/MATCH/MATCH, but seem to have used improper syntax.

    Being my first post, I'm going to try and include an image of the sample spreadsheet I'm testing on. The image doesn't match the above information, but if my upload works, should give you a better idea of what I'm trying to do. The lower data area surrounded by the black border has been manually filled to show the result I am seeking.

    Formula Problem.JPG

    TestBook.xlsx

    Thank you in advance for any help you can offer!

    Sincerely, David Brunk
    Last edited by David Brunk; 09-12-2014 at 01:00 AM.

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Dynamic search; using column # result to determine column used for column/row match.

    Could you pls upload your sample workbook not an image, click "Go Advanced" button and find paperclip button to attach your file

    Thanks

  3. #3
    Registered User
    Join Date
    09-11-2014
    Location
    Gloversville, NY
    MS-Off Ver
    MS Excel 2007
    Posts
    5

    Re: Dynamic search; using column # result to determine column used for column/row match.

    Absolutely; thanks! I've also added it to the original post.

    TestBook.xlsx

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Dynamic search; using column # result to determine column used for column/row match.

    It would be a lot easier if you could have all your data in a tabulat format, each month below the previos, but I guess that is not an option due to the data source?

    Try this in B22, copied down and across...
    =IFERROR(INDEX($A$12:$F$16,MATCH($A22,OFFSET($A$7,5,MATCH(B$19,$A$7:$F$7,0)-1,5,1),0),MATCH(B$19,$A$7:$F$7,0)+1),"")

    I have used the ranges you provided, but you will probably need to adjust these for your real data
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    09-11-2014
    Location
    Gloversville, NY
    MS-Off Ver
    MS Excel 2007
    Posts
    5

    Re: Dynamic search; using column # result to determine column used for column/row match.

    Talk about asking the right people the right question! Looks like I need to add IFERROR and OFFSET to my homework. :-)

    Works perfectly. Thanks for your help Ford!

    Sincerely, David

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Dynamic search; using column # result to determine column used for column/row match.

    Happy to help and thanks for the feedback

    IFERROR() is pretty simple, it's an improvement on the previous IF(ISERROR()) method of error trapping - very simple syntax...

    =IFERROR(your formula,what-to-do-if-your-formula-errors)
    If your formula works, then it's value is returned. If it doesnt, then the "what to do" bit kicks in

    OFFSET() is also relatively simple, see the help on it for the full syntax, but essentially...
    =OFFSET(start-point,rows-down-to-start-from,columns-across-to-start-from,how-high-is-the-range,how-wide-is-the-range)
    If you just want to retusn a specific value, the last 2 arguments can be left it

  7. #7
    Registered User
    Join Date
    09-11-2014
    Location
    Gloversville, NY
    MS-Off Ver
    MS Excel 2007
    Posts
    5

    Re: Dynamic search; using column # result to determine column used for column/row match.

    Hi Ford,

    Thanks for the additional tutorial...that was very helpful. Your explanations are far more succinct than the manual!

    Admittedly, I'm having a little difficulty converting the sample formula (that worked perfectly in that sample sheet) to my actual spreadsheet. I'm fairly certain the problem is my unfamiliarity with how the OFFSET perimeters need to change based on a different size sheet (which your post addresses). I'm also referencing from Sheet 1 to Sheet 2 in my actual model which adds to the "clutter".

    I'm going to do some further reading and poke at the formula a bit more, but may reach out with the actual sheet if I get stuck.

    Regardless, I'll re-post my results and let you know how things turn out.

    Thanks again for your help! David

  8. #8
    Registered User
    Join Date
    09-11-2014
    Location
    Gloversville, NY
    MS-Off Ver
    MS Excel 2007
    Posts
    5

    Re: Dynamic search; using column # result to determine column used for column/row match.

    Hi Ford,

    Just a quick note to let you know that I was able to successfully convert your cell formula to my financial model. It took a bit to figure out how the whole thing tied together, but I feel like I actually get it and can use these features to solve many problems in the future. Thanks again!!!!! David

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Dynamic search; using column # result to determine column used for column/row match.

    Its an awesome feeling when you work your own way through something like that - and the penny suddenly drops Thank you for the feedback, it is always appreciated

+ 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: 9
    Last Post: 11-19-2013, 06:48 AM
  2. [SOLVED] compare A and C column if match my logic display result in B column
    By vengatvj in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 11-03-2013, 02:07 AM
  3. [SOLVED] Search Value of column E if exist return value of column B put result in column P
    By hassan khansa in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-23-2013, 11:39 AM
  4. Lookup value in one column, match and return result from another column
    By raehippychick in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-13-2012, 03:26 AM
  5. Replies: 2
    Last Post: 06-23-2011, 11:45 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