In Google Sheets, I want to be able to calculate a dollar amount based on the time of day entered.
I'd like to enter the time in one cell and have the formula in another cell calculate the cost.
These are the time ranges:
Below is an example showing the result I want:
| A | B | |
|---|---|---|
| 1 | Time | Result |
| 2 | 3:30 | $ 6.00 |
| 3 | 4:00 | $ 9.00 |
| 4 | 2:37 | $ 3.00 |
How can I perform this calculation?
Spoiler
=VLOOKUP(A2, $D$2:$E$7, 2) // Basic
=ARRAYFORMULA(VLOOKUP(A2:A4, D2:E7, 2)) // Array
=IFNA(ARRAYFORMULA(VLOOKUP(A2:A, D2:E7, 2))) // Error Handling
This is an excellent candidate for the VLOOKUP function which looks for a value in the first column of a range, then returns the value in the same row, but in the column you specify.
It also supports approximate matches which is great for your needs. If your lookup table is sorted by time (ascending)VLOOKUP will find the largest match that is less than or equal to the lookup value.
Syntax
=VLOOKUP(search_key, range, index, [is_sorted])
All variations assume the following:
D2:E7 is the range containing your lookup table (rate sheet):
D2:D7 contains start times (column 1 of the range)E2:E7 has the rate for each start time (column 2 of the range)A2:A contains the time(s) you want to checkB2:B contains the formula result(s).| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Time | Cost | Increment | Rate | ||
| 2 | 3:35 | $ 6.00 | 2:36 | 3 | ||
| 3 | 3:00 | $ 3.00 | 3:06 | 6 | ||
| 4 | 3:45 | $ 9.00 | 3:36 | 9 | ||
| 5 | 4:06 | 12 | ||||
| 6 | 4:36 | 15 | ||||
| 7 | 5:06 | 18 |
This is the kind of formula that you copy down into other rows, so the range is absolute to stop it from changing when copied: $D$2:$E$7 vs. D2:E7
=VLOOKUP(A2, $D$2:$E$7, 2)
The ARRAYFORMULA function enables a single VLOOKUP formula to return the results for multiple rows. The search_key argument is changed to the range A2:A4
=ARRAYFORMULA(VLOOKUP(A2:A4, D2:E7, 2))
If you wrap the formula in the IFNA function, you don't need to worry about blank rows and can also use an infinite range such as A2:A. Any new rows of times you add would be automatically calculated:
=IFNA(ARRAYFORMULA(VLOOKUP(A2:A, D2:E7, 2)))
