Multi-Formula Help

ESch

Hello,

I am trying to use two columns compared to two columns in another sheet to produce the value of a third column.

For example, I need to produce the annualized revenue value if the client name and team member match.

So I have one master sheet with the client name, team, and annualized revenue amount, then another sheet broken down by client and team members and I want to get the annualized revenue amount per team.

Sheet 1- Master sheet

Sheet 2 - need the annualized revenue dispersed throughout the team members

The problem is the 100 limit on the cross sheet reference, I have 200+ cross sheet references I would need in order to use the "=IF(team@row=Dante2, {Summary Range 1}, 0)" formula.

Any suggestions?

James Keuning

When I am faced with a problem like this I create a column in each sheet that smooshes the two fields together:

Client@row+Team@row

Then use INDEX and MATCH to hook the two together and bring in your fields.

ESch

How would I use INDEX/MATCH to filter out the different team members revenue amounts into different columns?