I had to add values to a Google sheet depending on a list of others. (In my case I wanted to add salary ranges depending on the role of an employee). Of course this could be done with a large and nested if function but to be honest this really sucks.
After some research I found the nice filter functions that solved all my problems:
In my case
=FILTER(‚Salary ranges‘!$B$2:$B$6;‚Salary ranges‘!A$2:A$6 = B2)
Where Salary ranges was the sheet that has in col A the names of the roles and in column B the range of that role and in the example B2 in the role of the current employee.