The problem is you are trying to apply an 'OR' join....
I would probably create a new join column in your source excel sheet. In your CAD sheet do a vlookup on your Ama sheet based on Cad ID 1, and if that ID doesn't exist as an Ama ID, then do it based on Cad ID 2.
Then in Tableau you can do a straight forward join form your new join column to Ama ID 1.
Hopefully that makes sense.