Rreplace SUMIFS with INDEX/MATCH

[STEVEMILLS04]

I have read here: https://professor-excel.com/performance-excel-study/ that INDEX/MATCH or VLOOKUPS can be up to 50% faster than SUMIFS but for some reason I am having a hard time replacing a SUMIF formula with either Index/Match or Vlookup. Has anyone done this in the past?

=SUMIFS(D2:D8,B2:B8,"West",C2:C8,"<="&40)

Name	Region	Age	Dollars
Kyle	West	21	100
Tony	East	21	98
Lyle	East	36	54
Sean	West	46	14
John	West	47	13
Kevin	Central	54	98
Stephen	Central	58	250

<tbody>
</tbody>

 

[Gerald Higgins]

Hi, have you read the article on that site about the difference between VLOOKUP, SUMIF(S), and INDEX/MATCH ?

They are not completely interchangeable.

There are some situations where, for example, VLOOKUP and INDEX/MATCH can deliver identical results, and same for VLOOKUP and SUMIFS etc.
But these functions also have key differences which make them each useful in different situations.

In the specific example you have given, they will all provide the same results but that is because there is only one data item for WEST where AGE is less than 40.
But if you had more than one entry where AGE is less than 40, then SUMIFS would definitely be the way to go.

EDIT to add . . . actually, I don't think VLOOKUP is applicable to this situation at all, at least not easily.
And INDEX/MATCH again is applicable only with some difficulty.

 

[Fluff]

One way

Excel 2013/2016
	A	B	C	D	E
1	Name	Region	Age	Dollars
2	Kyle	West	21	100	100
3	Tony	East	21	98
4	Lyle	East	36	54
5	Sean	West	46	14
6	John	West	47	13
7	Kevin	Central	54	98
8	Stephen	Central	58	250
Sheet1

Cell Formulas
Range	Formula
E2	{=INDEX(D2:D8,MATCH(1,(B2:B8="West")*(C2:C8<40),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

But if you have a lot of data I'd tend to stick with the sumif

 

[STEVEMILLS04]

That makes sense and is what I thought. I guess I don't understand why they would compare the three when they are in fact not completely interchangeable. Thanks!