+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    01-12-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    2

    Cool 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!!!!
    Last edited by fnclplanner; 01-13-2012 at 05:22 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How to use Index & Match formula to lookup a field & post result on another works

    Something like:

    Please Login or Register  to view this content.
    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.
    Last edited by NBVC; 01-13-2012 at 12:49 PM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    01-12-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: How to use Index & Match formula to lookup a field & post result on another works

    It worked! I had to format the cells to percentage, and it's perfect. Thank you SO much NBVC!!! I never would have gotten that. You made my week!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1