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
raceproretiredAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor 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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Ian,

Please give this a try...

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)),""))

Open in new window

and then copy it across and down.
1
 
Rob HensonFinance 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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
raceproretiredAuthor 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
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
That's because I used absolute reference i.e. $L$14:$X$14

Please try this instead...
=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)),""))

Open in new window

1
 
raceproretiredAuthor Commented:
Thanks Guys, two perfect solutions.
Ian
0
 
Rob HensonFinance AnalystCommented:
Glad to be of help!
1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Ian!
1
All Courses

From novice to tech pro — start learning today.