Thread: How to use Index & Match formula to lookup a field & post result on another worksheet

Hi,
I'm new to the forum. I've have never had to ask for Excel help before, so please let me know if I've posted this in the wrong place, etc., and I appologize in advance if my terminology isn't correct. I've learned Excel 'on the job' the last 10 yrs and never had any training in it. I tried half a dozen formulas, but I'm getting errors: N/As, Ref!, and 0 & other #s that aren't the correct results. I can't figure out what I'm doing wrong.

Here's my dilemma: I have a 2 worksheet workbook. On worksheet "data feed worksheet from Orion", I import a report that looks like this:

worksheet 1.jpg

(Column A) (Column B) (Column C) (Column D) (Column E)
CLIENTNAME ASSET CATEGORY CATEGORY % ACCOUNT VALUE AS OF DATE
Jane Client Alternatives 24% $1,000,000.00 12/30/11
Jane Client Cash 13% $1,000,000.00 12/30/11
Jane Client Blend 4% $1,000,000.00 12/30/11
Jane Client Fixed Income 43% $1,000,000.00 12/30/11
Jane Client Equity 16% $1,000,000.00 12/30/11
Jason Doe Equity 88% $2,000,000.00 12/30/11
Jason Doe Cash 12% $2,000,000.00 12/30/11
John Smith Cash 2% $1,500,000.00 12/30/11
John Smith Alternatives 47% $1,500,000.00 12/30/11
John Smith Equity 28% $1,500,000.00 12/30/11
John Smith Fixed Income 23% $1,500,000.00 12/30/11

On worksheet "target vs actual", it is set up to compare the client's target % for each asset category vs the actual (category % listed on the "data feed worksheet from Orion" worksheet. It looks like this:

worksheet 2.JPG

(Column A) (Col. B) (Col. C) (Col. D) (Col. E) (Col. F) (Col. G) (Col. H) (Col. I) (Col. J) *
Client Account Value Equity Target Equity Actual % Difference $ Diff. Blend Target Blend Actual % Diff. $ Diff.
Jane Client $1,000,000.00 20% 16% -4% -$40,000.00 5% 4% -1% -$10,000.00
Jason Doe $2,000,000.00 85% 88% 3% $60,000.00 0% 0% 0% $0
John Smith $1,500,000.00 30% 28% -2% -$30,000.00 0% 0% 0% $0

*Columns K-N are for Fixed Income (Target, Actual, % Difference, $ Difference), O-R are for Alternatives, and S-V are for Cash.

I have the account value, % difference, and $ difference formulas set. What I need help with is the formula for the '_(asset category)_ Actual' columns (D, H, L, P, and T) on the "target vs actual" worksheet.

It should look up the combination of the client name plus the relevant asset category on the "data feed worksheet from Orion" worksheet (ie Jane Client + Equity), pull the Category % associated with it (ie 16%), and display it in the '_(asset category)_ Actual' column (ie D2) on the "target vs actual" worksheet.

Thanks in advance for your help!!!!

Something like:

=Sumproduct(--('data feed worksheet from Orion'!$A$2:$A$100=$A2),--('data feed worksheet from Orion'!$B$2:$B$100="Equity"),'data feed worksheet from Orion'!C$2:C$100)

adjusting ranges to suit....

Note: "Equity" can be replaced with a cell reference containing the word (if one exists in the "target vs actual" sheet.

you can then copy formula down and/or across...

Posting a workbook sample would make it easier to evaluate.