How to adapt an existing multiple instance index match (To only return matched d

Question

How to adapt an existing multiple instance index match (To only return matched data)

Hi there

I have a table that auto populates sales data based on sales agent names. I have formulas which auto populate agent names based on team manager name entered into cell B4:

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+1),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+2),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+3),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+4),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+5),"")

The data in Sheet1 has a master table with team managers names in B:B and agent names in C:C - team manager name appears next to each agent name as appliccable.

There are 6 agents in the first team.

There are more than 6 agents in other teams, the total number being 12 in the larger teams.

My question is as follows...

Can i evolve the formulas to become template formulas? For example i have the same formulas in each table, but only show results when a match is found?

To explain the problem further - if i use the formulas:

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+6),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+7),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+8),"") etc

...in the first table, i get names appearing from the next team rather than a blank cell, even though the IF statement is set up to return a blank if no match is found. Is this because the additional part of the formula to return multiple instances (+1, +2, etc) is somehow overriding the match? (even though i am asking the match to return exact matches (0)?

I understand that i can just use the relevant amount of formulas for each team, and alter the formulas if team sizes change, however my OCD will not allow only semi auomated syntax to be acceptable!

So as an end result, id like to be able to have a table with spaces for 12 agents in total, and formulas to return data only if is relevant (agent is in the team)

**Please note no files can be shared due to sensitive data, but please ask for more clarification if needed**

Does anyone have any ideas on how i can solve this issue?

Is there an alternative formula that i may be missing that will perform the actions i need more efficiently than the way i am trying to do it?

Many thanks as always.

Explanation / Answer

...Change

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+1),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+2),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+3),"")   ...etc

To:

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0))=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0)+1)=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+1),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0)+2)=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+2),"")

=IF(INDEX(Sheet1!B:B,MATCH(B4,Sheet1!B:B,0)+3)=B4,INDEX(Sheet1!C:C,MATCH(B4,Sheet1!B:B,0)+3),"") ...etc

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.