Spreadsheet For Work

Occasional Visitor

Hello All. I have not used Excel for over 20 years and now I find myself stuck.

Im basically trying to come up with a function where numbers 1-20 have a dollar value.

IE. Cell A name is Zone, That zone ranges from 1-20

Zone 1 is worth $32

Zone 2 & 3 is worth $44

Zone 4 is worth $48

so on and so forth.

 

what im looking at doing is to have a function where Cell B, which is Pay Rate automatically populates the correct dollar amount to track income. Can anyone help me? I've been trying to figure it out for a few days now and have come to the conclusion that I need some help to figure it out. Anyone? Help Please. Thanks in Advance.

1 Reply

@DENNIS92 Easiest would be to create a table with two columns, Zone and Rate. Zone contains all  numbers from 1 to 20, and the Rates column the appropriate rate for each zone. Don't bother to group the zones like 2&3 with one rate. That just makes it more complicated.

 

Then you can use VLOOKUP to find the appropriate rate based on the zone. That formula would something like:

=VLOOKUP(zone, zone_table, 2, FALSE)

 

Where zone refers to a cell where you want to find the rate for and zone_table refers to the range or table name for the table I described above.

 

A lot has changed since you last used Excel. So, If you happen to be working in a more recent version now, you might even have the XLOOKUP function at your disposal.