Return a Value Using Multiple Sources
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(B 2,Sheet2!A6:A10,1),C2+1)))
would be a 2 level...
would get it.
"eed" wrote:
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!!
|