another vlookup q

I am trying to create a formula in a single cell using VLOOKUP based on 2 separate sets of criterea. Basicall what I would like to accomplish is that if a cell returns "#N/A", then use the next piece of data to fulfill the vlookup.

In the example attached, the info in the "data to pull from" area contains partial info. The formula should be entered into C3. Hope this makes since on this fine Friday.