Index + Multiple Match Conditions??

Hi everyone

I have found this Forum really great as it has encouraged me to try and do much more than I have done in the past with Excel. Despite buying a few books and searching through various Excel support sites, I still seem to come unstuck at some point.

My latest problem is best seen by viewing the REMOVED worksheet in the attached workbook.

I am trying to create a formula that will generate the total from row 10 in the COLLECT column according to the date shown in D12.

As can be seen from the result in D13, I have managed to use INDEX and MATCH to extract the first occurrence of the Totals figure (L10) within the worksheet, using a defined range (Table). However, no matter how many ways I try to combine an additional MATCH with the date (D12) and Row 2, nothing works or produces the correct result.

Although the test cells are located on the REMOVED worksheet; they will actually be located on different worksheets but it seemed to locate them on the same worksheet for testing.

Hopefully, there will be someone who knows the answer to this one.

Thanks in advance …Spellbound

Your table is a little awkward but try this:

=INDEX(1:10,10,MATCH(D12,2:2,FALSE)+2)

This formula seems to be working:

Please Login or Register  to view this content.

And you need to change the range for Table. In your example you have not included row 2 (where the date field is), you have started on row 3.

Hi Reafidy

Thanks for your quick response.

You mentioned that my 'Table' was a bit awkward but your formula worked fine, which negated the need for the defined name 'Table'.

I have incorporated your formula within my workbook, using it from different worksheet sources and all the results check out fine. I have even made use of it with the adjoining cell for one of the other worksheets.

The only drawback that I have found is that when the number of rows increase, the 2nd argument of the formula will need changing manually, unless you know of way to take this into account.

So based upon your example, if the worksheet grows to 20 rows, then the rows in the first argument change automatically but the second argument remains the same.

=INDEX(1:20,10,MATCH($D$12,2:2,FALSE)+2)

whereas it needs to be:

=INDEX(1:20,20,MATCH($D$12,2:2,FALSE)+2)

I was very surprised as to how much shorter and simpler your solution was to my attempts.

Thanks again ...Spellbound

Hi Bjornar

Thanks for your solution.

Could'nt get it to work at first until I realised your version of Excel uses ; whilst mine uses , so I just changed all of them to , amended the range and everything worked fine in the test workbook.

I will test it more fully tomorrow in the main workbook.

Thanks again ...Spellbound

My formula should take care of your problem with expanding tables. It looks up "TOTALS" to find the correct row. All you have to do is to make sure the Table range includes all your rows. There should be now problem leaving this range bigger than the actual range in use just to be ready for future exandings of your list.

Sorry about the ; , mixup. I work in Norway and , is used for desimal separator here. But the biggest problem with Excel in Norway is that some idiot at Microsoft desided that in the Norwegian version they have translated all the formulanames to Norwegian This means that if I type a formula I have to translate everything to Norwegian

Hi Bjornar

I can understand your frustration as it must make a lot of extra work for you translating all the formulas and functions.

I have now integrated your solution into my work book with an expanded range, which also meant I could do the same for the adjoining column.

As usual, this has now led to an idea of a further useage of this procedure but I need to fully understand the individual aspects of the formula better.

Please Login or Register  to view this content.

The bit that I can't get my head round is what the , and numbers represent after 'Table' i.e. Table,,1,1

I have looked in some of my books but none of them actually spell it out in plain English, even though they have used this as the basis for examples.

Would appreciate any feedback on understanding this better.

Thanks again ...Spellbound

I couldn't find some good reference for this but the result is this:
INDEX(Table,,1,1) returns an array of the values in the first column of Table
INDEX(Table,1,,1) returns an array of the values in the first row of Table
This version of INDEX uses 4 parameters instead of 3 parameters as the other version of index in this formula uses.

I came up with this result by using the insert function whizard. In the Function arguments Window you can experiment with different arguments and se the result of the function.

Argument 2 is row number, and if omittet the whole column is returned.
Argument 3 is column number, and if omittet the whole row is returned.
Argument 4 i came up by experimenting in the Function arguments window. I saw that if I set this to 1 the function returned an array. This was the array I needed.

Another good help for analysing and understanding large formulas like this is using Tools -> Formula Auditing -> Evaluate formula.
Then you can resolve the formula step by step and se the result of each sub function.

Hope this is of some help for you. Let me know if you need some more explanations.

Bjørnar

Thanks Bjørnar for your explanation. As I said, I could not find any specific sources that spelled out the arguments.

I will explore this situation more fully at the weekend to see if I can adapt it for my latest project, which will entail using cell references for the row and column arguments. Should be fun!

Once again, thanks for your help.

Spellbound