Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have struggled mightly with this assignment....Assume the following grid:

    Code:
    5	   0  Below Minimum Range
    5  36,800  First Quartile
    5  41,400  Second Quartile
    5  46,000  Third Quartile
    5  50,600  Fourth Quartile
    5  55,201  Above Maximum Range
    4	   0  Below Minimum Range
    4  32,000  First Quartile
    4  36,000  Second Quartile
    4  40,000  Third Quartile
    4  44,000  Fourth Quartile
    4  48,001  Above Maximum Range
    The first column is the pay code (i.e. the 5's and 4's). The second column is base compensation threshold amount and the third column is the commentary that I want to retrieve to a cell when looking up values in the first 2 columns.
    EDIT: Somehow when I type this it looks like coulmns...but when it posts in the lounge it is run together

    For example, if someone is a grade 5 and making less than 36,800, I need to retrieve the value in the third column, "Below Minimum Range". If someone is a grade 5 and making 41,399, then I would retrieve the 3rd column value of "First Quartile". I actually have about 20 pay grades with the 6 amounts if this impacts your recommendation.

    I "played" the last 3 hours with vlookup, index and match and have only produced #NA's, #Value's and other various sundry Excel error messages....The only thing I believe that I have concluded [hopefully correctly] is that what I need to do cannot be accomplished with a vlookup function and nested if statements...I ran up against the if statement limit.

    This caused me try to use match function, then the index function and finally a combination of the index with match function...but to no available. I have experimented with the true or false switch in these functions too. I have never used these functions before and could not apply some of the stuff I searched in the forum to successfully produce the required result.

    There really should be a courtesy function coder in EXCEL that helps one out after he has produced 100+ error messages trying to apply a formula / function in one session...I have certainly exhausted my know-how [this was in minutes] and now my patience [this in 3 hours] with the so-called help file.

    Any assistance is greatly appreciated. Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Say that the data you posted are in A1:C12, and that you enter the grade to be looked up in H1 and the wage in H2.
    The description is given by the formula

    =VLOOKUP(H2,OFFSET(B1:C6,MATCH(H1,A1:A12,0)-1,0),2)

    B1:C6 is the lookup range for grade 5.
    MATCH(H1,A1:A12,0) returns the row number where the grade is first found in column A.
    OFFSET(B1:C6,MATCH(H1,A1:A12,0)-1,0) is the lookup range to be used.

    See attached workbook.
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    An alternative approach to that offered by Hans is offered in the attached file. With this approach it is necessary to create a named range for each pay category.
    Attached Files Attached Files
    Regards
    Don

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hans and wdwells..thank you both for a reply...I will study both examples as I really need to master these functions as I can now see how powerful they can be...I have especially struggle as the pay code column is always an exact match but the compensation match is to a "range" and determining how EXCEL handles this situation [i.e. not an exact match] has been extremely difficult for me. Again, thank you both.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FWIW, you could also rearrange your data table a bit, as in the attached.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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