More elegant than index-match + index-match

[bmont]

A	B	C	D
1	100	200	300
2	400	500	600
3	700	800	900

To add line 2 of Head1 and Head3, my go-to would be:

=index( B:B, match( 2, A:A, 0)) + index( D:D, match( 2, A:A, 0))

...which seems clunky bc of the repeated matching.

Is there a more elegant way to do this, like what you'd imagine a SUMSIF would do if it existed?

 

[jasonb75]

With a single match

=SUM(INDEX($B:$D,MATCH(2,$A:$A,0),N(IF({1},{1,3}))))

If the data is sorted by column A and it is known that the match criteria will always be found in the list then using approximate match instead of exact would be a better improvement to the formula.

Also you could try limiting it to a realistic number of rows. If you have less then 1000 rows of data then over 99.9% of the match effort is being wasted.

 

[pgc01]

Hi

Also with a single match, try also:

=SUM(SUMIF(A:A,2,OFFSET(B:D,,{0,2})))

 

[chistos]

You can try XLOOKUP, it should do a job if you combine two

 

[bmont]

Thanks for your answers, @jasonb75 and @pgc01 -- that's some next-level stuff!

 

[Peter_SSs]

@bmont
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

In relation to version, the simpler version of jasonb75's formula shown in F1 works for me in Excel 365. (Also, since your values in column A are in ascending order and you are looking, exactly, for one of those values you do not need the final argument in the match function)

Whilst it is not as short a formula overall in this case, the new LET function in Excel 365 (not all subscribers have it yet) does allow for a simplification of repetitive calculations within a formula a shown in G1.

20 10 14.xlsm
	A	B	C	D	E	F	G
1	1	100	200	300		1000	1000
2	2	400	500	600
3	3	700	800	900
Sum

Cell Formulas
Range	Formula
F1	F1	=SUM(INDEX(B:D,MATCH(2,A:A),{1,3}))
G1	G1	=LET(Rw,MATCH(2,A:A),INDEX(B:B,Rw)+INDEX(D:D,Rw))

 

[jasonb75]

In relation to version, the simpler version of jasonb75's formula shown in F1 works for me in Excel 365.

Perhaps one of the benefits of dynamic arrays, it doesn't work in 2019 without the extra coercion.

 

[Peter_SSs]

.. it doesn't work in 2019 without the extra coercion.

I assumed that was the case hence my reference to the significance of version information, but thanks for the confirmation.

 

[bmont]

I'd hope to see solutions posted for all versions, irrespective of my own. Indeed, I still learn stuff from million-year-old posts, so LET(rip) your advanced-version posts!

That said, still wondering if there's a solution that would behave like what you'd expect from a SUMSIF function, a la IF( INDEX(rangeA)=TRUE, SUM( INDEX(rangeB) and INDEX(rangeC), 0).

Also, =IF("rephrasing the question"<>OK,"sorry, i'm noob",)