Using the MATCH-INDEX function to retrieve data.

Hi everyone.

I have with me a list of expenses that I have on the left (Columns A to C). These are expenses I expect to pay based on Date with a value.

I want to create an Index-Match formula that will retrieve all expenses “AFTER” any date on cell F3. For this example, I put in 7/31/2015 on cell F3. In this case, F4 to F10 has the formula “=INDEX(G:G,MATCH(TRUE,G:G>A2,0))” that will give me any date after, but it has an issue with 2 expenses are paid on the same date. It only pulls the top one. Is there a way to fix this to give me BOTH expenses, their type and cost for that date.

Example here, 8/31 Rent is not pulled because it is after Doctors App.

I want to avoid having to do a pivot table…

Any help on this would be greatly appreciated.

Thanks.

Hi Richard,

Yes, my first post. I know Pivot tables "ok", but my boss really wants to see the dropdowns and this format for some reason.. the old man is set that there must be a way and it is easy.. this is a much simpler example of a bigger thing I am trying to do.. Do populate a timeline with dates of everything I have left to pay at any point in time F3. My problem is not solved.

Essentially, I have future costs on some excel tab.. I want at any point.. to be able to see what else I have left to pay, what date is due, what type of expense and how much. So, when cell F3 changes, he wants all cells from F4 to H10 to auto populate with all expenses that I have left to pay. The issue comes when I have two expenses to pay on one day. index/match only picks up one.. leaves out the one below it.

Any help on this would be greatly appreciated.

Thanks.

This answer is dependent on the data being arranged sequentially by date as in the sample file. F4 contains the array formula (Ctrl + Shift + Enter)

Please Login or Register  to view this content.

which dragged across also populates G4:H4. I4 contains the array formula

Please Login or Register  to view this content.

which gives the row number of the next date, expense type and cost. F5 contains the formula

Please Login or Register  to view this content.

which can be copied across to column H and then down as far as needed. You will need to change the formatting in column H back to currency afterwards. Here is your file with these formulas included, note that I put a simple data validation drop down in F3, which could be easily improved by creating, and hiding, a pivot table of dates from column A.

Copy of Index - Match Question.xlsx

Let me know if you have any questions.

Hi JeteMc and Other,

Thank you for your help. I greatly appreciated. I have two questions as I want to understand what you did and I am also doing this exercise in two separate tabs. I have attached the excel file V2 (separate tabs)

1) If I try to replicate the formula in a separate sheet, meaning, having a data tab and one summary tab in another, it does not work. Can you tell me what I am doing wrong? It is driving me crazy. I have highlighted the one you did in a green tab, and the separate ones in a red tab.

2) What does the -5 on the last formula stands for? what is it doing?

Many Thanks.
Andres.

The formula was written to work with the one page set up that was included with the original question. The -5 means that the formula will look 5 columns to the left to find the date, expense type and cost. A modification of the formula to fit the "separate tabs" scenario would be:

Please Login or Register  to view this content.

As seen in the file:Copy of Index - Match Question_2.xlsx

Thank you JeteMc. Problem Solved.

You're welcome and thanks for the feedback. Hope that you have a good day.