Vlookup to the left

I am looking for a way to essentially perform vlookup to the left. The workbook I am working in makes the most sense how it is now and the information I want is to the left instead of the right.

I have tried index and match but I couldn't get it to work and the array I want to be pulling from is not static. I routinely add information and would need to pull the new information. To further complicate matters, the table I want to pull the data from is in one sheet and where I need the data is on another sheet.

I have attached a small workbook I am trying to get the CARD# on Sheet 2 to "autopopulate" with the CARD # from sheet one.

Any assistance would be appreciated.