Two conditions meet, One condition is within data set, Search for corresponding value

Hello,

This one has been making my eyes bleed from my brain.

I know it's gotta be some variation of Sumproduct, Index, and Match, but I'm not figuring it out.

I can get the value in "C2" with this formula:

=SUMPRODUCT(--(INDEX(data,,1)=A2),--(INDEX(data,,17)=B1),OFFSET(INDEX(data,,17),,1))

However, the values are mixed up too much for anything linear to exist such as defining "which column" to look under. This part will unfortunately be variable depending on when someone gets skilled.

I'm in search of a formula that will give me the same result even if the data is in column 16, or 20, or 35, or 2, etc.

This is an actual report I run to find skill levels in a call center, but I'm trying to use sheet 2 as a "copy and paste" worksheet and leave the job to sort this information up to sheet 1.

If you're not opposed to INDIRECT

Please Login or Register  to view this content.

Glad to help If you wouldn't mind, it would help my reputation out if you'd click the scales next to my post (also remember to mark the thread as solved if the solution meets your needs). Thanks! BTW...I love Salt Lake City!

No worries This is from the Forum rules page:

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

Thanks!