+ Reply to Thread
Results 1 to 15 of 15

Nesting Vlookup Functions with IF Functions and possibly more

  1. #1
    Registered User
    Join Date
    03-25-2014
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Nesting Vlookup Functions with IF Functions and possibly more

    Hi Everyone,

    I am really hoping someone will be able to help me with this, I will explain what I am trying to do just in case you can suggest a better way for me to achieve the same outcome.

    I have a master spreadsheet (MSS) which has rate details of employees, each employee falls into a banding code e.g. Joe blog's banding code is 123, 123 is a banding of $100 to $300 for African on a 5 hour day, all my coding references are on a separate spreadsheet, what I need to do work out the percentage in which Joe Blogs rate sits with in a particular banding.

    What I am finding hard is the fact that I need excel to reference the employees location and hours per day to then reference the correct banding to then determine the percentage the persons rate sita in the banding.

    At present I have done the following:
    - The MSS and the bandings in the same document
    - I have the following formula to work out where the rate sits in the band:
    Top End - Bottom End = Range
    Rate - Bottom End = X
    X / Range x 100 = Percent (figure I want to show against each person)

    What I need this formula to do is reference the persons location, hours and code, then work out the percentage their rate sits in the banding.

    The formula I have started to try is:

    =IF(AND([@[Point of Origin]]="Africa",Table3[[#Headers],[Hours Per Day]]="5"),S11=VLOOKUP([@[Job Classification Code]],'Africa!D11:P226,4,FALSE),)
    this is not working it just comes up with a "0".... and I am not even sure if this is the best way to do this?

    PLLEEEAAAAASSSEEEEEEE HELP ME

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Registered User
    Join Date
    03-25-2014
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    Hi Zbor,

    Please see attached, I hope you can help.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    Where is this Banding that you looking in other sheets?

  5. #5
    Registered User
    Join Date
    03-25-2014
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    The other TAB in the SS is the bandings "Africa 2013" column "D", "Job ID" are the banding codes.

    Sorry for the confusion, please let me know if you have any other queries.

    Thanks again for your help, it is much appreciated

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    I assume it's Job ID but I was confused because in first sheet criteria is 12345 and in Job code it's 1234

  7. #7
    Registered User
    Join Date
    03-25-2014
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    Apologies, this is because I have used examples rather than the actual data

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    np, it's OK to use dummy data but it need to be unified.
    Can you please check this example.

    1. Name Point of Origin same as sheet names (Africa changed to ASfrica 2013)
    2. Change order in sheets from biggest (11 Hour Diff) to lowest (8 Hour Diff)

    I return now Candidate Base Day Rate divided with higher range
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-25-2014
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    Sorry Im not sure if I understand what you want me to update?

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    Check solution.
    If not OK update desired output.

  11. #11
    Registered User
    Join Date
    03-25-2014
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    Apologies, I cant see what you have done, what I need is the mss to refer to the banding for rate reference then calculate a percentage in where the persons rate sits within the banding.

  12. #12
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,404

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    What I did is formula in Contractor Details V column

  13. #13
    Registered User
    Join Date
    03-25-2014
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    I did look very quickly at this when I was in the office before so I must have missed this as I was in a rush, sorry. I do not have a access to the document now, I will check tomorrow. Thanks again

  14. #14
    Registered User
    Join Date
    03-25-2014
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    WOW!!! you are amazing, thanks.... now if I want to apply this formula onto my actual document what would the best way bee to do this?

  15. #15
    Registered User
    Join Date
    03-25-2014
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Nesting Vlookup Functions with IF Functions and possibly more

    This is the formula I have used below: replacing your references with my columns, I tried putting the column e.g. A2 however the formula auto changes it to the column name. I have matched what you have done titled the TABs to match the point of origin however for some reason it is just coming back with #REF

    =[@[Candidate Base
    Day Rate (incl. Super)]]/INDEX(INDIRECT("'"&[@[Point of Origin]]&"'!D7:J222"),MATCH([@[Hours Per Day]],INDIRECT("'"&[@[Job Classification Code]]&"'!D7:J222"),0),MATCH([@[Hours Per Day]],INDIRECT("'"&[@[Point of Origin]]&"'!D7:J222"),-1))

    What am I doing wrong?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Nesting VLOOKUP functions
    By CroatWonder in forum Excel Formulas & Functions
    Replies: 52
    Last Post: 09-06-2005, 07:05 PM
  2. Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 AM
  3. Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  4. [SOLVED] Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  5. [SOLVED] Nesting functions in the functions dialog box
    By cs170a in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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