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

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

Hi Zbor,

Please see attached, I hope you can help.

Thanks!

Where is this Banding that you looking in other sheets?

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

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

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

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

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

Check solution.
If not OK update desired output.

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.

What I did is formula in Contractor Details V column

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

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?

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?