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
Bookmarks