0

I want to match the dates in Column A to the Closest date in Column E (if a date in column A doesn't match to dates in Column E then I want to return the next available date in Column E and I've done with this INDEX MATCH formula:

=INDEX(INDEX(E:E, MATCH(A3,E:E,1)+1,1)

What I'm having trouble with is putting the Expenses Title and values in the range E:G from the range A:C. The "Output Expenses Title" and "Output Values" Column should contain values from Expenses Title (Column B) and Values (Column C) based on the Closet date Matched (next greater date in case not exactly matched).

I think it would be done using the Query function but not sure. And apologies I'm new to StackExchange and wasn't able to provide a comprehensive description of my problem. I've also made changes in the attached spreadsheet to better explain it.

https://docs.google.com/spreadsheets/d/1LR_Z2cJo1ZqGMJcHCJFr7dA7t3jYnTrjvNZuDYTshp8/edit?usp=sharing

Improve this question
13
  • 1
    According to your post and your sample spreadsheet examples, you want the dates in Column A matched to the CLOSEST date in Column E. However, this will leave some of your expenses falling before the Column E dates... and some falling after; it would seem the ones falling after would be LATE, wouldn't they? Are you sure you don't want the Column A dates matched the closest date in Column E WITHOUT going past the Column E date? – Erik Tyler Dec 5 '20 at 21:35
  • Welcome. Would you please add a brief description of your search/research efforts as is suggested in How do I ask a good question?. – Tedinoz Dec 6 '20 at 1:26
  • Sorry if I'm not clear and hopefully my spreadsheet can explain it better. You should NOT rely on your spreadsheet to provide a better explanation. Anyway, your spreadsheet does NOT explain it better. The example in Column H:J has nothing to do with your question. Rather than making things clear, it is confusing. Would you please edit your question AND your spreadsheet to describe the outcome that you are trying to achieve. Your spreadsheet should include an example of a successful outcome. – Tedinoz Dec 6 '20 at 1:26
  • Edited my post to explained it better. Thank you all for pointing out. and @ErikTyler I want the dates in Column A to be matched to the Closest date in Column E (if not exactly matched then the next available date in Column E). – Jumanji ji Dec 6 '20 at 16:44
  • @Jumanji ji, I'm afraid this is still not clear. You have said two different things: 1.) "I want the dates in the Column A to be matched to the closest date in Column E" and 2.) "if not exactly matched, then the next available date in Column E." Those are not the same thing. Let's say you had 12/11/20 in Col A, and 12/09/20 and 12/16/20 in Col E. The CLOSEST match is 12/09/20 (which is BEFORE 12/11/20); but the "next available date" is 12/16/20 (which is AFTER 12/11/20). – Erik Tyler Dec 7 '20 at 5:27
0

@Jumanji, I have added a sheet ("Erik Help") to your sample spreadsheet. There you will find two different array formulas, each highlighted in bright green. Each of these formulas is independent from one another.

The first formula (in F1) references your list of dates in E:E and your original data set in A:C. Here is that formula:

=ArrayFormula({"Expense Title","Value";IF(E2:E="",,IFERROR(VLOOKUP(E2:E,{IFERROR(VLOOKUP(FILTER(A2:A,A2:A<>""),E2:E,1,FALSE),VLOOKUP(FILTER(A2:A,A2:A<>""),E2:E,1,TRUE)+7),FILTER(B2:C,A2:A<>"")},{2,3},FALSE)))})

HOW IT WORKS:

The ArrayFormula(...) wrap signals that the one formula will process an entire range, not just one cell.

Headers are generated, followed by a semicolon (which means "Place the next data below").

If there is no date in E2:E, such rows are left blank.

Next, you will see a set of VLOOKUPs within VLOOKUPs. First, we'll concentrate on the inner set captured between curly brackets:

{IFERROR(VLOOKUP(FILTER(A2:A,A2:A<>""),E2:E,1,FALSE),VLOOKUP(FILTER(A2:A,A2:A<>""),E2:E,1,TRUE)+7),FILTER(B2:C,A2:A<>"")}

This forms a virtual array that says (in plain English), "First try looking up every date in A2:A within E2:E and try to find an exact match. If there is an exact match, return it. If there is not an exact match, look up that date again and just fall back the closet date before in the E2:E list, then add 7 days to that. Then, to the right of this new array of exact or next-closest dates, place all the data from B2:C that has a date in A."

Once this virtual array is in memory, we run the outer VLOOKUP for every date in E2:E. Exact matches will return Columns 2 and 3 from the virtual array. Any dates in E2:E that find no match will fall to IFERROR and return nothing.

The second formula (in I1) uses only the data in A:C to generate a report of only relevant/filled dates. It does not require a list of dates to match against. Here is that formula:

=ArrayFormula({"Date","Expense","Value";IF(A2:A="",,FILTER({MROUND(A2:A,7)-(WEEKDAY(DATE(YEAR(TODAY()),1,1))-1) + IF(A2:A>MROUND(A2:A,7)-(WEEKDAY(DATE(YEAR(TODAY()),1,1))-1),7,0), B2:C}, A2:A<>""))})

HOW IT WORKS:

As above, the ArrayFormula(...) wrap signals that the one formula will process an entire range, not just one cell.

Headers are generated, followed by a semicolon (which means "Place the next data below").

If there is no date in A2:A, then the formula will return nothing.

Note next the outer FILTER(...,A2:A<>"") wrap. This will assure that only data in A2:C that has something in Column A will be processed. It is necessary, because you have other data ("Total" and an amount) in Columns B and C below the data set. (By the way, placing non-parallel date below other data is not good practice for a range you are planning to further process.)

You will notice several instances of "7" throughout the formula. That is because every viable date falls exactly one week (i.e., 7 days) apart.

Within the curly brackets, you'll see that another virtual array is formed. Most of it is modifying the actual dates in A2:A to fall in increments of exactly one week starting on January 1 of the current year. MROUND does this once. Then, we test to see if that rounding landed the A2:A dates in one of two categories: 1.) on or after the A2:A date (in which case add nothing) or 2.) before the A2:A date (in which case add 7 to bump it to the next week. To that list of modified dates, we simply add the rest of your B2:C data to complete the chart.

I also added another sheet ("Erik Help 2") that contains a variation of the second formula above which also includes the "Total" line at the bottom.

Improve this answer
2
  • That's Exactly what I was after. Thank You Very Much. And apologize for the confusion that I caused by not explaining my question better. If I've more questions, I'll try to explain them much btter and with complete details. – Jumanji ji Dec 7 '20 at 14:15
  • @Jumanjiji, glad we got it figured out. This is your first post. You will get better at asking questions. I know that is your intention. – Erik Tyler Dec 7 '20 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.