Iif function

Good afternoon

I require an Iif function created in a query, so it will probably be SQL specific. The function needs to relate to a lookup table where the value is equal to that of two fields.

Because I record the Officer's collar number in my database the lookup table has independant values but when you reference it against the main table you cannot create a relationship as obviously this would create duplicates. Is there a function/expression that we can write that you reference the two fields with the collar number and if that collar number matches, then references another field with the force unit description. It ideally needs to be placed into a query as the data will be imported on a regular basis. And, I want it to auto workout the details from the query.

So, this is how I want it to work:

Iif ([Officer] = [txtOfficerNumber], Show [txtOfficerUnit], "Unknown"]

Is there a more productive way to action this query. Hope you can help?

Gary