Formula required for autocompleting a cell once data validation item selected

Ok so this is my first post here so some patience would be greatly appreciated and of course if this has been answered elsewhere previously then by all means send me in that direction.

Im building an estimating template to price small construction projects. I'm looking to make the spreadsheet populate itself for the best part, based on what items are selected from the drop down validation list. Screenshot looks liiiiike.....

So the contents of the labour rate card (Cells I5 to K11) are to remain fixed and static at the top of the spreadsheet.

Cells I18 and I19 contain a drop down box with different labour role referring to the contents of I5 to I11.

What i'm looking to achieve is that when I select a labour type from the drop down box in cell I18, the labour rate cell in K18 will autocomplete with the corresponding numerical value. Then I can lock down everything from K18 down to prevent accidental rate mishaps.

Brilliant. Using alphabetical order is a small price to pay. Thanks mate.

Originally Posted by gmr4evr1

Maybe this in K18

Formula:

Please Login or Register  to view this content.

*NOTE The problem with this formula is that the left most column must be sorted lowest to highest so "Supervisor" would have to be last on the list or it wont work correctly