INDEX MATCH Help

  • Hi guys,


    Tried to vlookup and hlookup , that seemed to not work so Index Match seems best.


    I have different sensitivities across the horizationally( ex: -30,-20, 0, 20, 30) in one sheet and vertically in another. Any way to index match this?


    [ATTACH=CONFIG]66932[/ATTACH]
    In this screen shot the portfolio name will be listed once becasuse sensitivites go horizontal.


    [ATTACH=CONFIG]66933[/ATTACH]
    In this screenshot, portfolio names are listed multiple times because sensitivities go vertical.


    My goal is to provide a side by side comparison so I can calculate differences differences between these 2 sheets.
    Any ideas would be appreciated

  • Re: INDEX MATCH Help


    Hi Wonka,


    I'm afraid it's a bit hard to view your images and understand what you want. Could you provide the workbook with either the sensitive information replaced with dummy data, or removed?

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: INDEX MATCH Help


    Quote from Luke M;756388

    Hi Wonka,


    I'm afraid it's a bit hard to view your images and understand what you want. Could you provide the workbook with either the sensitive information replaced with dummy data, or removed?


    forum.ozgrid.com/index.php?attachment/66936/


    Okay I attached a sample book.
    See I want to line them up, so port 1 goes with port 1, and so on. It needs to search for the portfolio name because some wont exist sometimes.
    Index match would help do a side by side comparison. Is this helpful?

  • Re: INDEX MATCH Help


    Thank you. Looks like the best option is actually to create a PivotTable, if I'm understanding how you want the layout to go. The Pivot lets you easily get the layout you want, and it will do all the calculations you need. Note that I'm not quite sure where the values you had were coming from.

  • Re: INDEX MATCH Help


    If there's only a 1-to-1 matchup, then yes, you could build such a thing. Would need to build 4 true/false arrays to check the all criteria, find the item within all 3 arrays that was true, and index against the values. If you have multiple values and/or have some spots that will be blank, things get even more complicated.


    IS there a reason you don't want the ease of the PT?

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: INDEX MATCH Help


    The ease, well , i dont know how to use pivot tables really, I was able to do one, but to combine 2 sheets anyway and then do differences among the items I care about is another story. ( I dont care about the items that have 0 values or dont exist in both sheets).


    Just thought index match could line up the values nicely in the 2nd sheet.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!