Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

# Index match when the condition is true

Brass Contributor

# Index match when the condition is true

Hi there,

Can someone help me build a logic to accomplish below requirement:

There are two tabs in excel wb: Sheet A and Sheet B as shown in images

I want a formula in Sheet A(column E), which will look for set# 01.111.120 in sheet B (column A) then It should check the condition in (column G) Rec. total equipment >0 for all occurrences, which is "yes" in this example, because out of 3 instances for this set #, for one instance it's 2, then it should return Total procedures from Col C (sheet B),which is 8 in this case

4 Replies

# Re: Index match when the condition is true

Does the attached sample file return the intended result? The formula works with IF and SUMIFS instead of INDEX and MATCH.

# Re: Index match when the condition is true

No, I just highlighted 8 and 10 in this case because that's the value the formula should return. Can you please share the logic which can help to get the result? Thanks!

# Re: Index match when the condition is true

You are welcome. Unfortunately it seem that i don't understand the logic of the task. Perhaps an expert of the community can help you with this.

best response confirmed by AKuma0411 (Brass Contributor)
Solution

# Re: Index match when the condition is true

Hello,
I kind of answered my own question, this formula worked as per my expectation
=IFERROR(SUMIFS('Sheet B'!\$C:\$C, 'Sheet B'!\$A:\$A, 'Sheet A'!\$B2, 'Sheet B'!\$G:\$G, ">0"), "")

Thanks!
1 best response

Accepted Solutions
best response confirmed by AKuma0411 (Brass Contributor)
Solution

# Re: Index match when the condition is true

Hello,
I kind of answered my own question, this formula worked as per my expectation
=IFERROR(SUMIFS('Sheet B'!\$C:\$C, 'Sheet B'!\$A:\$A, 'Sheet A'!\$B2, 'Sheet B'!\$G:\$G, ">0"), "")

Thanks!