CODEWITHSUNDEEP
excelexcel-formulaexcel-2007
FartStopper
FartStopper

Reputation: 121

Use Index Match Match To Find Multiple Values

I have a spreadsheet that will have the text "Number Returned" multiple times in a header row (running from A1-Z1, but will expand and search area will be larger). From A2:A55 I have names, I need a way to match the name to each instance of "Number Returned" and get a sum. I see I can use =Index(Match(Match)) to find one instance of "Number Returned" but could that formula be altered to add to a SUM() formula?

Here are a few snippets to show what I want (the total would need to be on a secondary worksheet not the same one if that matters)

enter image description here

enter image description here

Upvotes: 1

Views: 140

Answers (1)

Scott Craner
Scott Craner

Reputation: 152495

You can simply use this SUMIF formula against your data:

=SUMIF($A$1:$M$1,"Number Returned",A2:M2)

by placing it in N2 and copying down. (you can adjust the end range and formula placement)

To have the formula on a separate sheet, the formula provided by @ScottHoltzman in the comments may work better.

=SUMIF(Sheet2!$A$1:$M$1,"Number Returned",OFFSET(Sheet2!$A$1:$M$1,MATCH(A2,Sheet2!$A$1:$A$7,0)-1,0))

The range qualifications in the formula are based on your example data with the following notes:

  • Sheet2 refers to where the actual data is
  • assumes the reference list is columns A:B of with Header (Total Return) being row 1

As it was @ScottHoltzman who came up with a better answer, this has been made a community wiki.

If avoiding volatile formulas is a concern, the following replaces the OFFSET function's role with the non-volatile INDEX function.jpd

=SUMIF(Sheet2!$A$1:$M$1, "Number Returned", INDEX(Sheet2!$A:$M, MATCH(A2, Sheet2!$A:$A, 0), 0))

Upvotes: 2

Related Questions