google spreadsheets switch statement

Question

I'm trying to calculate a daily salary in a google spreadsheet.

in this particular job the hourly wage depends on the total amount of daily sales.

here are the specifics:

Sales - Hourly

$0.00 - $8.00

$350.00 - $9.00

$400.00 - $10.00

$500.00 - $11.00

$600.00 - $12.00

$700.00 - $14.00

$800.00 - $16.00

$900.00 - $18.00

$1,000.00 - $20.00

$1,300.00 - $23.00

$1,600.00 - $26.00

$2,000.00 - $30.00

for instance : if the sales rep sells $545 he will get $11 per hour untill he breaks the $600 mark.

in another sheet the employees input their check in and check out time, and their daily sales.

i have extracted the daily work hours and i would like to implement a switch statement equivalent that will calculate the hourly wage depending on the daily sales.

and then simply multiply them and calculate the daily salary.

thanks in advance.

Answer 1

You want a VLOOKUP here. Off my mind, taking the sales column is A and hourly rate column is B and they have no headers (12 rows per value):

  =VLOOKUP(400, $A$1:$B$12, 2, TRUE)

The 400 there would be the employee daily sales (you can change for a cell data).

More info about VLOOKUP here: https://support.google.com/docs/answer/3093318

PS: the first column needs to be sorted (that's the TRUE last argument) and be formatted as numeric (not as text), but I guess you already have that. If the list is not sorted, or is not numeric VLOOKUP only matches the exact search key