How to return pay value looking at two criteria

[treydawgmt]

Hello,

I am trying to return the base pay value for part time firefighters, based on their certification level (FF/EMT-B or FF/EMT-P) as well as step raise level (1-6). I have attached my spreadsheet, really I'd like to check against C5 & E5 to get to the value down in the D28:K30 area. I've tried (unsuccessfully) to get there with IF(AND statements, as well as INDEX & MATCH thoughts. I think my brain is just fried today though, so that could be it.

Thanks!

[protonLeah]

Please Login or Register  to view this content.

[ChemistB]

First I put a formula into column E for Step. In E5

Please Login or Register  to view this content.

Then in L5 copied down

Please Login or Register  to view this content.

You need to make sure that "FF/EMT-B" is consistent (sometimes you have the dash, sometimes you don't)
Also, for today's date, the final 3 people are now Step 1. If that's not the case and there's some sort of delay, we need to include that in the Column E formula

[treydawgmt]

Thanks guys! I think I was missing one of the matches or something, I tried something very similar, but now I can't remember what I did that made it not work and yours work.

ChemistB - the reason I didn't have steps auto picked, is that it will allow us to know when we manually update their pay. Even in 2020, the village's pay software requires you to enter pay increase dates, and reflects the step increases, but doesn't actually pay more money without a complicated process to actually make the pay happen.... That's the whole point of this spread sheet, to make up for that problem!