# 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?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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: