# Add matching condition to existing formula

Hi,
I would like a formula modified to replace a manually input matching cell
Please see attached sheet with explanation.
Many Thanks
Ian
Matching-Names.xlsx
###### Who is Participating?

Finance AnalystCommented:
Presumably the values in columns L to S will change as you go down the column to give different results in columns C to F. If so slight amendment to the above formula:

=IF(\$A14="","",SUMIFS(\$J\$14:\$J\$41,\$H\$14:\$H\$41,F\$13,\$I\$14:\$I\$41,SUMIF(\$L\$13:\$S\$13,F\$13,\$L14:\$S14)))

This has removed the row lock on the L14:S14 range in the SUMIF so that it adjusts as you fill down columns C to F. Also added the lock to column A when copying across.

See attached with some more entries in columns L to S on the assumption above.
Matching-Names.xlsx
1

Excel & VBA ExpertCommented:
Hi Ian,

In C14
``````=IF(\$A14="","",IFERROR(INDEX(\$J\$14:\$J\$41,MATCH(C\$13&INDEX(\$L\$14:\$X\$14,MATCH(C\$13,\$L\$13:\$X\$13,0)),INDEX(\$H\$14:\$H\$41&\$I\$14:\$I\$41,0),0)),""))
``````
and then copy it across and down.
1

Finance AnalystCommented:
Assuming its always numeric results, try in cell F14:

=IF(A14="","",SUMIFS(\$J\$14:\$J\$41,\$H\$14:\$H\$41,F\$13,\$I\$14:\$I\$41,SUMIF(\$L\$13:\$S\$13,F\$13,\$L\$14:\$S\$14)))

The first SUMIFS looks at column J for a value where column H matches the name in row 13 and looks at column I for the value resulting from SUMIF from L14:S14 where the name matches in L13:S13.
1

retiredAuthor Commented:
Hi Neeraj, The formula copies down the same values. The values when copied across were correct.
please input some values 0-3 in range L15:S15 and copy formulas down
Thanks
0

Excel & VBA ExpertCommented:
That's because I used absolute reference i.e. \$L\$14:\$X\$14

``````=IF(\$A14="","",IFERROR(INDEX(\$J\$14:\$J\$41,MATCH(C\$13&INDEX(\$L14:\$X14,MATCH(C\$13,\$L\$13:\$X\$13,0)),INDEX(\$H\$14:\$H\$41&\$I\$14:\$I\$41,0),0)),""))
``````
1

retiredAuthor Commented:
Thanks Guys, two perfect solutions.
Ian
0

Finance AnalystCommented: