VLOOKUP with multiple criteria

[tkbuc]

I am trying to use a VLOOKUP formula with multiple conditions to pull back a Sales Rep’s name from a master listing based on an order #. I have attached a mock draft of what I am using.

Goal: Populate Sales Rep name in column A based on Order # (col C) and/or Package # (col B).

Condition 1: In the master listing (cols J-K), if the Order Number is not found or if the formula result for the Sales Rep name is blank, I need the formula to do a lookup based on the package number.
Condition 2: If in my lookup list, if a Sales Rep name was accidentally placed in the Order # field (col C), I need the formula to lookup by package # first in the Order # field (col K) and if nothing is found, lookup in the Package # field (col J) in the master listing.
Condition 3: For true blank or not found items, the result should be “Unassigned”.