index match multiple variables

[ExcelSur]

Good Day,
I have attached my data sheet with expected result. I tried with index and match functions that I saw on this forum with no luck. I have to upload the expected result into an accounting software. I also tried using pivot tables with no success.

Thanks

 

[bosco_yip]

Try,

In S5, copied down :

=SUMIF($A$2:$A$297,LEFT($R5,6),INDEX($B$2:$N$297,0,MATCH(MID($R5,8,99),$B$1:$N$1,0)))

Regards
Bosco

 

[ExcelSur]

Try,

In S5, copied down :

=SUMIF($A$2:$A$297,LEFT($R5,6),INDEX($B$2:$N$297,0,MATCH(MID($R5,8,99),$B$1:$N$1,0)))

Regards
Bosco

Hi Bosco
Thanks for taking your time and helping me on this issue. Can you make one adjustment. I am adding other titles and when I copy the formula down I get #N/A error.

Thanks

 

[Peter Bartholomew]

My formula is similar to Bosco's but I didn't bother to split the 'Title & Pay Description' to get strings I could match. Since 'Auto' will be in column 2 every time you look it up, I cheated and gave it the answer in a helper column 'columnNumber'. The other catch was returning 'Office' as the filter criterion. Again, since it is the same for the entire table, I used it as the table heading and named it.

= SUMIFS( INDEX(Table1, 0, columnNumber), Table1[Title], Title )

I am not sure how much use the attached file will be to you since I used the problem to explore some challenges I encountered with the new dynamic arrays. Two solutions worked (using SINGLE to pick up the column by relative referencing and an old-style CSE formula) and the third (dynamic array) gave value errors.

For Bosco's formula to work on the other titles, you will need to change the hard-wired values derived the length of the word 'Office' and instead search for the space to determine the length of the current 'title'.

 

[bosco_yip]

Hi Bosco
Thanks for taking your time and helping me on this issue. Can you make one adjustment. I am adding other titles and when I copy the formula down I get #N/A error.

Thanks

Then, try this revised formula as in

In S5, copied down :

=SUMIF(A:A,LEFT(R5,FIND(" ",R5)-1),INDEX(A$1:N$1,0,MATCH(MID(R5,FIND(" ",R5)+1,99),A$1:N$1,0)))

Regards
Bosco

 

[Peter Bartholomew]

Just to demonstrate that 'non-standard' approaches are possible.
The purpose of the array formula in this instance is simply to ensure that each entry in an output table picks up the table title as a relative reference.

 

[ExcelSur]

Then, try this revised formula as in

In S5, copied down :

=SUMIF(A:A,LEFT(R5,FIND(" ",R5)-1),INDEX(A$1:N$1,0,MATCH(MID(R5,FIND(" ",R5)+1,99),A$1:N$1,0)))

Regards
Bosco

Thanks Bosco. Trying to understand how all these works. Thanks for taking your time and helping me

 

[ExcelSur]

Just to demonstrate that 'non-standard' approaches are possible.
The purpose of the array formula in this instance is simply to ensure that each entry in an output table picks up the table title as a relative reference.

Hey Peter,
I like the way you named your ranges. I understand how the SUMIFS formula works now. I am getting this error when I copy and paste the formula from Office to Batchman. Thanks for taking your time and helping me on this. Thanks

 

[Peter Bartholomew]

Hi, you have been caught out by the array formulas. Whereas the default ad-hoc development style of relative referencing is highly optimised to make user interaction easy, the conceptually simpler idea of array formulas is fraught with user-hostile features, starting with the fact that the formulas are committed using a three-key combination (Ctrl+Shift+Enter).

In this case the whole of each 'debit' column is determined by a single formula. You cannot change it cell by cell; it is all or nothing. The error message you encountered is particularly pernicious; once triggered it just doesn't seem to want to go away. The simplest strategy is to hit 'escape' and re-plan.

To alter the debit column, or come to that the 'Title and Pay Description' column, you either select the first cell, edit and finally commit with CSE, or you can select the entire range, nothing in-between works.

The layout is designed so that the entire block can be copied and pasted as a single unit and any pay group typed into the title bar will propagate through the formulas. Without the array formulas, I would need a separate absolute reference for each pay group or, alternatively, to hard-wire the names into the formulas.

I hope this helps explain what is going on.