A multiple criteria match formula

[pezalmendra]

Hi!

I am trying to find up a way of combining some match and lookup criteria in a formula but cannot end up in a successful result. Its a bit complicated (for me) and maybe someone can help me a bit on this.

I have attached and example sheet explaining better what I am looking for.

Any help would be much appreciated.

Kind regards,

pezal

[Pauleyb]

1) Arrrgggh! Why use text for your times in the tables? Convert to numeric with the time format.

2) Additionally, convert your two date and time columns into a single date time column and use Excel's formatting to show the date and time. This will make it so much easier to make comparisons.


3) I don't understand your example statement:
... add values from the blue columns in which C3 + D3 (from Sheet1) is in between them. For this cell the answer will be the cells L6 + M6 +L7 + M7 from Sheet2

In your spreadsheet, C3 + D3 is 8/2 9:45
In table A, row 6 date time is 7/29 8:25 and row 7 date time is 8/2 9:30.
I don't see what is 'between'

[pezalmendra]

Hi Pauley,

Thanks for your time and reply.

What I mean is that C3 + D3 is 2 of August 9:45.
In table A, this date is between row 6 (29 July 8:25) and row 7 (2 Aug 9:30) and so I would like to copy the values from columns L and M from those rows.

I dont know if maybe is easier to do it with a VBA code...

Thanks once again.

Regards,

[Pauleyb]

But it is not between those dates, correct? A few things which need clarification:
when you say 'date' do you mean just the date or the date + time? I think you mean date + time, and, if so, August 2 9:45 is greater than both of those dates and times, correct?
If you just mean date, then Aug 2 is not between July 29 and Aug 2. Which brings up the question, do you mean you also include dates (or date+times) that are equal to ones in the table?

If you convert your date and times into a single number, I think I have a decent solution: I would use a dynamic range that picks the correct table, then a SUMIF that uses INDEX with the dynamic range to do the calculations.

If we can agree on the date range and you provide a sheet that has the dates and times combined, I believe I can provide a solution.

[Pauleyb]

Hmmm, I started to create a demo, and then see another problem with your request - not that it is a bad request, it is just that your data and layout prevent a 'simple' solution. The desire to sum 4 cells in a 2x2 array poses some problems. I'll have to think a bit on that one...

[Pauleyb]

Here is my solution. So much is dependent on making things easier in the layout versus the formula. I ended up creating a column in the tables which is the sum of the 2x2 matrix. Also, it is written as I interpret it: Aug 2 9:45 is between rows 7 and 8 in your original table, not rows 6 and 7.

Note: I only formatted Table A in this proof of concept. The other tables would need to be modified (combine date and time as one cell, have a column for begin date, a column for end date, and a column for the sum of the 2x2 matrix).

[shyamhappy]

Hi

I have VBA code for you. Please have a check this will provide your data.

Please Login or Register  to view this content.

If you need any amendments, please let me know.



_______________________________________________________________________________________________________________________________________________________________
if you feel somebody has helped, please click * to add Reputation

“I have not failed. I've just found 10,000 ways that won't work.”
― Thomas A. Edison

[pezalmendra]

Pauley, you are so right! Sorry for the confusion, what you have done is exactly what I was looking for.

The 2x2 column is okay! But it is possible to avoid joining the date and time together in a cell? Also the ones from the table ("begin" and "end").

ALso, I don't know if its possible is to use the LOOKUP function for searching the correct table (instead of giving a name to each one). I say this because the number of table will be constantly changing; now I have 7 but next will there will be 8 or 9... It would be good to have a fix formula or name which doesn't need to be modified each time I add or delete a table.

I know its a bit messy and confusing but the solution you gave me gives with the result and I am trying now only to adapt it as much as possible to what the worksheet will be at the end.

Thanks again, I really appreciate your help.

Regards,

pezal

[pezalmendra]

Hi shyamhappy,

Thanks a lot for your code!! My second option was searching for a macro and maybe this one can result also. I will give a look to it!!

Regards!

[Pauleyb]

But it is possible to avoid joining the date and time together in a cell?

Sure it is possible, but it could make the equation more difficult. For example, let's say your first date in the table is
Aug 1 10:00
and the second is
Aug 2 9:00

If these are separate cells and one cell just contains the date and the other contains just the time, then the task to find if a date is between them is much more difficult. Think of the logic to determine that Aug 1 11:00 is between them. A solution would be to have two cells with the same number (dates and times are just numbers to excel with a specific format). So, each cell would have the same number, but you would format one cell to just show the date and the other cell to just show the time through formatting. This would allow a quick and easy check to see if another date is between the two.

Also the ones from the table ("begin" and "end").

Having the begin and end dates in the same row make it easier for the SUMIFS function, since it looks for elements with the same index in the arrays it uses. The INDEX technique I'm using returns entire columns. I thought of sizing it to your tables but felt that your tables would have varying length, so I avoided it. There are ways, but it becomes more complex. Maybe VLOOKUP and MATCH type of formula gymnastics.

ALso, I don't know if its possible is to use the LOOKUP function for searching the correct table (instead of giving a name to each one). I say this because the number of table will be constantly changing; now I have 7 but next will there will be 8 or 9... It would be good to have a fix formula or name which doesn't need to be modified each time I add or delete a table.

Well, I didn't create a table name for each table - it was a dynamic named range. I did, however, hardcode it to just the table count you had, but others could be added.

One of my initial thoughts was to use OFFSET. With a varying number of tables, that may be the easiest and most straightforward method, but offset can get bogged down if there are LOTS of instances. Something to consider, but I am off for the weekend now.

[Pauleyb]

OFFSET was the way to go. Here is the formula
=SUM(OFFSET(Sheet2!$L$5,MATCH(C3,OFFSET(Sheet2!$B$6:$B$11111,0,14*(CODE(D3)-65))),14*(CODE(D3)-65),2,2))
But, I did convert the date and time cells into one value. Still two cells, since that seems to be the format you prefer, but formatted the left cell to just show the date and the right cell to just show the time. But, for comparisons, each cell is the full date and time value.
In the attached file, I only updated the date/time cells in table A. You need to do the other tables.

[pezalmendra]

Hi Pauley!

Sorry for the delay but Ive been busy these days...

This is very good! Thanks a lot! Finally you end up with a short formula which is really good.

Thanks once again for your help and dedication.

Regards!