VLookup 2 columns (one being conditional)?

[helpneeded2]

I have two separate worksheets that I am working with. In sheet #1, I want a field that will pull in an account number from the sheet #2.

There are 3 specific columns in the 2nd sheet that will be referenced:

Account Number
Customer Number
Status

The customer number is a unique identifier, and there can be multiple instances of the account number associated with the same customer number. The inactive accounts have "cancelled" in the Status column.

I would like a formula from Sheet #1 that will lookup the Account number from Sheet #1, and compare it against the list of Account numbers in Sheet #2, and return the associated Customer Number for the first instance it finds of the Account number, where the Status is not "Cancelled".

Is this possible to do with a lookup function, and if so could someone please let me know how this could be done?

Thank you.

 

[GraH]

Seems you are on 365. Google excel filter function.