Index Match multiple rows

Hi,

I have company accounts with rows listed by its general ledger code (e.g.: 19999 for revenue, 39999 for overheads) and columns by whole months in 2019.

The following code returns me the value for the general ledger code 45999 for the month column it sits in ('Date' = Oct-19):

=-INDEX('TB'!F:Q,MATCH(45999,'TB'!B:B,0),MATCH(Date,'TB'!F1:Q1,0))

However I want to return multiple general ledger codes, in theory it would look like this, adding the different general ledger code results together:

=-INDEX('TB'!F:Q,MATCH({45999,46350,47956,47283},'TB'!B:B,0),MATCH(Date,'TB'!F1:Q1,0))

This of course does not work.

The only way I have managed this is using hlookup with the row number the general ledger code sits on, for example 45999 sits on row 437:

-SUMPRODUCT(HLOOKUP(Date,'TB'!F:Q,{437,467,486,475},0))

This is inefficient as you can't add in new rows without messing up this formula. I am looking for a way to achieve this without using much more resources than the above hlookup uses. Can anyone help?

My apologies, please see the attached.

D30=SUMPRODUCT(($F$1:$Q$1=D26)*(ISNUMBER(MATCH(C5:C20,E26:E28,0))*(F5:Q20)))

Another way

D30=SUMPRODUCT(SUMIFS(INDEX($F$5:$Q$20,,MATCH(D26,F1:Q1,0)),$C$5:$C$20,E26:E28))

Originally Posted by Glenn Kennedy

I assume that you want the SUM of the matching values.

=IFERROR(SUM(INDEX($F$5:$O$20,N(IF(1,INDEX(MATCH($E$26:$E$28,$C$5:$C$20,0),0))),MATCH(D26,$F$1:$O$1,0))),"")

will do that. A dynamic Named Range would also be useful for $E$26:$E$28 to adjust the number of terms searched. Do you know howto do that?

Don't use whole column references - use sensible "future proof" ones, unless you really do have 1,000,000 rows of data. Some functions (e.g. arrays, SUMPRODUCT, etc) will get very slow.

Excellent thank you Glenn, the I.T manager and I have been looking at this for an hour trying to get our head around what's happening in the row part of the 1st index function.

Instead of the dynamic named range I just used an array and it worked fine, I'd prefer this over a range:

=IFERROR(SUM(INDEX($F$5:$O$20,N(IF(1,INDEX(MATCH({47762,46350,49562},$C$5:$C$20,0),0))),MATCH(D26,$F$1:$O$1,0))),"")

We're trying to understand what the 2nd index is doing. The first argument is a reference of the multiple row numbers for 47762,46350,49562? But then what is the 2nd argument doing? It's just a zero. We've only ever used Index with the first argument being an array.

Secondly the N function. Without it the formula only returns the value for 47762, with it included it allows the sum function to sum the separate values for 47762,46350,49562. How is this function achieving this?

Thirdly why is the 2nd index function required? I would have expected it to have worked with a match function alone to get the row argument for the 1st Index function.

Fourthly, what is the IF function doing? I assume it's checking if something is True but what is it checking?

Originally Posted by CARACALLA

D30=SUMPRODUCT(($F$1:$Q$1=D26)*(ISNUMBER(MATCH(C5:C20,E26:E28,0))*(F5:Q20)))

This is great as well, thanks Caracalla. I used the array in place of the range and it worked fine without having to coerce Excel into accepting Enter. Is this because it's using sumif instead? I'm going to use this instead as it'll be easier on future users.

=SUMPRODUCT(($F$1:$Q$1=D26)*(ISNUMBER(MATCH(C5:C20,{47762,46350,49562},0))*(F5:Q20)))

Originally Posted by Glenn Kennedy

I assume you have followed Excel's merry journey using Formulas/Formula auditing/evaluate formula

If not - doso. it's amazing what you canlearn. It is limited, though. the box cannotbe resized and you cannot copy/paste from it. That said, if you have a small range - you really can see what is happening.

I've never actually heard of it but I just googled "Formulas/Formula auditing/evaluate formula" and it looks interesting. Will give it a read now.