Lookup values in a table with Matching

  • Hello,


    I have the following dataset. What I am trying to accomplish is lookup and match the value in the first column. Once the value is found, use the numbers from that row to calculate the results. Examples are below:
    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    Values>Threshold

    [/td]


    [td]

    0%-35%

    [/td]


    [td]

    36%-60%

    [/td]


    [td]

    61%-90%

    [/td]


    [/tr]


    [tr]


    [td]

    A

    [/td]


    [td]

    6,000

    [/td]


    [td]

    2,000

    [/td]


    [td]

    2,500

    [/td]


    [td]

    3,600

    [/td]


    [/tr]


    [tr]


    [td]

    B

    [/td]


    [td]

    8,000

    [/td]


    [td]

    5,000

    [/td]


    [td]

    5,200

    [/td]


    [td]

    6,000

    [/td]


    [/tr]


    [tr]


    [td]

    C

    [/td]


    [td]

    12,000

    [/td]


    [td]

    1,000

    [/td]


    [td]

    1,200

    [/td]


    [td]

    1,500

    [/td]


    [/tr]


    [tr]


    [td]

    D

    [/td]


    [td]

    11,000

    [/td]


    [td]

    8,000

    [/td]


    [td]

    9,000

    [/td]


    [td]

    9,500

    [/td]


    [/tr]


    [tr]


    [td]

    E

    [/td]


    [td]

    5,000

    [/td]


    [td]

    12,000

    [/td]


    [td]

    15,000

    [/td]


    [td]

    15,500

    [/td]


    [/tr]


    [/TABLE]


    Now for the results, they will be in three separate columns as below (calculation logic is listed underneath the results)


    [TABLE="width: 500"]

    [tr]


    [td]

    500

    [/td]


    [td]

    1,100

    [/td]


    [td]

    4,400

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1,000

    [/td]


    [td]

    500

    [/td]


    [td]

    9,500

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    If "A" is found then the formula would be take values from left column 2 of the first table as such, value in column 2 is 6,000. Thus, if 6,000>2,000, then take 2,500 - 2,000 and the answer would be 500 in column 1 of table 2 (please see above). Because 6,000 is also greater than the value in last column for this row i.e. 3,600, for the next two columns in the second table the values will be 1,100 and 4,400 (total would be the sum of the three columns i.e. 500+1,100+4,400 = 6,000), calculated as such (3,600 -2,500 = 1,100) and (6,000 - (500+1,100))=4,400


    If the value in column 1 is "E" then the rest of columns would read NA or Zero, as the number (5,000) is smaller than the value in column 3 (12,000).


    Another example, let's see in row 3 of table 2 we get "D", then the values the columns would be 1,000 (9,000-8,000 as 11,000 is > 8,000), next cell will be 500 (9,500-9,000) and the last cell value will be 9,500.


    Thank you for looking this over. I very much appreciate all the help I can get. I was able to use vlookup to get some part of this work, but was having trouble getting the the complete formula to use the values in the same row. For exmaple, in the first table, let's assume at a later time, I wanted A to be changed to C, the first part using vlookup would find the correct value, but then would still be stuck on the first row rather than matching the values from Row with "C" as in input.