Index Match with proration of upper and lower values.

  • Hi experts!


    i am trying to use match to get upper and lower values of the horizontal and vertical data but i am only able to get the lower data and not the upper data.


    The purpose of getting upper and lower data is to prorate them and get the pro-rated value from 4 value. (upper & lower value of x-axis AND upper & lower value of y-axis)


    having the 4 values and prorate accordingly and now it seems like i am able to only get one value instead of all the 4 value to prorate.


    Attached is the excel sheet for reference.


    index_match.xlsx

  • You can use vlookup for this. Have one cell as the input cell, and then vlookups below it for each piece of information you want to bring up.

    In your formulas you will want to use wildcards, so you can put in part of a name.

    One issue with this is of you put in a partial name, it will return the first match, and you will have to re enter more of the name.

  • Hello WL,


    The problem with the way you are using the match function is that match requires ascending order for match type 1 and descending order for match type -1. If you are able to mirror the data some how you might be able to get around this, I will have a look for an alternative solution.


    HTH

    Justin

Participate now!

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