Return a Value Using Multiple Sources

[eed]

I need to return a value (a $AMOUNT) based on the following information:
TITLE, LEVEL 1 2 3 or 4, and a percentage range.

Ex: If the [TITLE, ie: Store Manager] exceeds their sales plan by a
[PERCENTAGE RANGE, ie: 1-5%], then they receive a certain bonus [$AMOUNT]
based on their [LEVEL].

The static information is currently set up in a chart as follows:

Level 1 Level 2 Level 3 Level 4
Title 1
Percentage Range 1 $X $X $X $X
Percentage Range 2 $X $X $X $X
Percentage Range 3 $X $X $X $X
Percentage Range 4 $X $X $X $X
Title 2
Percentage Range 1 $X $X $X $X
Percentage Range 2 $X $X $X $X
Percentage Range 3 $X $X $X $X
Percentage Range 4 $X $X $X $X

The variable/monthly info is in a basic spreadsheet format and contains the
info that tells me who is entitled to receive a bonus, but ultimately I need
to return the $AMOUNT they should receive based on the chart above.

Help? Thanks!!

 

[Sean Timmons]

OK, some f thsi will depend on where your Title and Level are in your result
sheet. So, we'll assume title is in A2, Percent over target is in B2 and
Level # is in C2

Let's assume the first table is for Store Manager and is on sheet2 in cells
A1 - E5.

=IF(A2=Sheet2!A1,INDEX(A1:E5,VLOOKUP(B2,Sheet2!A1:A5,1),C2+1))

You'd have to nest if's for each title available in the above, so:

=IF(A2=Sheet2!A1,INDEX(A1:E5,VLOOKUP(B2,Sheet2!A1:A5,1),C2+1),IF(A2=Sheet2!A6,INDEX(A6:E10,VLOOKUP(B2,Sheet2!A6:A10,1),C2+1)))

would be a 2 level...

would get it.

 

[Sean Timmons]

Oh, and important note, the percent rage in yoru source should be the low end
of your range (i.e. - .01 then .05, etc.), not a range of numbers. the
VLOOKUP will return the lowest value that is less than or equal to your B2
cell n that example.

 

[eed]

I've tried this and think I almost have it, but the formula doesn't seem to
be recognizing the percent ranges. (I did what you said below regarding this
and only put the low end of my range in in each of those cells...still
doesn't seem to work.) I am getting a $value returned for lowest end of the
range and above; the dollar amounts are all coming from the $value in the the
cells next to the first percentage range (Which should be 5% to 6.99%, but is
now in my table as 5.00%, or the lowest value in my range) See below - this
is how 'SHEET 2' is set up:

TITLE (A1) Level 1(B1)Level 2 Level 3 Level 4
5.00% $X $X $X $X
7.00% $X $X $X $X
9.00% $X $X $X $X
11.00% $X $X $X $X
TITLE (A6) Level 1 (B6)Level 2 Level 3 Level 4
5.00% $X $X $X $X
7.00% $X $X $X $X
9.00% $X $X $X $X
11.00% $X $X $X $X

SHEET 1 is set up as follows:
TITLE (A1) % OVER SALES PLAN Bonus Level Bonus Amount
5.00% 2

-2.00% 4

Thank you so much for all of your help!!

Oh, and important note, the percent rage in yoru source should be the low end
of your range (i.e. - .01 then .05, etc.), not a range of numbers. the
VLOOKUP will return the lowest value that is less than or equal to your B2
cell n that example.

I need to return a value (a $AMOUNT) based on the following information:
TITLE, LEVEL 1 2 3 or 4, and a percentage range.

Ex: If the [TITLE, ie: Store Manager] exceeds their sales plan by a
[PERCENTAGE RANGE, ie: 1-5%], then they receive a certain bonus [$AMOUNT]
based on their [LEVEL].

The static information is currently set up in a chart as follows:

Level 1 Level 2 Level 3 Level 4
Title 1
Percentage Range 1 $X $X $X $X
Percentage Range 2 $X $X $X $X
Percentage Range 3 $X $X $X $X
Percentage Range 4 $X $X $X $X
Title 2
Percentage Range 1 $X $X $X $X
Percentage Range 2 $X $X $X $X
Percentage Range 3 $X $X $X $X
Percentage Range 4 $X $X $X $X

The variable/monthly info is in a basic spreadsheet format and contains the
info that tells me who is entitled to receive a bonus, but ultimately I need
to return the $AMOUNT they should receive based on the chart above.

Help? Thanks!!