my case here.
in SaleOrder sheet, if product is chosen, then get the product commision rate from the employee sheet which is according to the employee name in the SaleOrder sheet.
if Service is chosen, then get service commision rate

this below only able to get the commision rate accroding to the employee. How do i compare again with either product or service?

=VLOOKUP(B2,Employee!$A$2:$C$5,3,FALSE)