Cell reference depending on age date of birth

[singe]

If you are not aware, in the UK we have National Minimum Wage that varies on your age.
The ranges are 16-17, 18-20, 21-24, 25+
I have members of staff of varying age that can move through the various rates of pay.
I have a cell with the member of staff's date of birth. I have grid of cells with the 4 ages, start date of birth, end date of birth and finally Rate of pay.
I'm grateful for any help in a cell forumla for =TODAY() and then IF maybe nested if to find the rate of pay for that date of birth range. Or amybe Index(match mightbe better.
I'd prefer not to have VBA as I have no experience in that.
Hope you have all the info you need.
Thank you in advance

 

[WaterGypsy]

I'm not sure what exactly you have in your grid for pay rates ..... but I can see a couple of ways to do it based on what you say.

The simplest might be to use SUMIFS comparing the DOB on the employee line against the start and end dates on your grid.

Alternatively you could change your grid so you have a line for each age and then do a vlookup using the employees age to find the rate

 

[singe]

I'm not sure what exactly you have in your grid for pay rates ..... but I can see a couple of ways to do it based on what you say.

The simplest might be to use SUMIFS comparing the DOB on the employee line against the start and end dates on your grid.

Alternatively you could change your grid so you have a line for each age and then do a vlookup using the employees age to find the rate

Thanks, I will look into those options. I suspect the latter might be simpoler for me to do.

 

[singe]

Thanks, I will look into those options. I suspect the latter might be simpoler for me to do.

Just by way of an update, thanks to another sire I used HLOOKUP
=HLOOKUP(C2,$AD$2:$AG$4,3,TRUE
I had to re order my the dates so it was in chronological order in the grid, and used the earlier age in the the row before the latter but apart for that t worked well.