Update UserForm label text based on dropdown and formula

Hi,

I looking to create a UserForm to generate location coordinates. Please see example attached.

Clicking 'Receipt' brings up a UserForm. The idea is that you would scan in the item number, select the room location, and that would flash up the assigned sublocation. Clicking 'Mark as Received' would then transfer that information to the bottom of columns A/B/C.

The formula for calculating the sublocation can be found in cell F11. I've found it very difficult to convert this to a VBA formula, or know how to use it properly in VBA.

Any help would be massively appreciated.

Many thanks,
Jamie