# Nesting Vlookup Functions with IF Functions and possibly more

1. ## 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. ## 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. ## Re: Nesting Vlookup Functions with IF Functions and possibly more

Hi Zbor,

Please see attached, I hope you can help.

Thanks!

4. ## Re: Nesting Vlookup Functions with IF Functions and possibly more

Where is this Banding that you looking in other sheets?

5. ## 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. ## 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. ## Re: Nesting Vlookup Functions with IF Functions and possibly more

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

8. ## 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

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. ## Re: Nesting Vlookup Functions with IF Functions and possibly more

Check solution.
If not OK update desired output.

11. ## 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. ## Re: Nesting Vlookup Functions with IF Functions and possibly more

What I did is formula in Contractor Details V column

13. ## 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. ## 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. ## 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?

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

#### 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