Combine If with Index Match

I am trying to have my formula use the index match function if an adjacent cell contains the word "Inventory". If that same cell contains "Retired" then I need the formula to perform an index match with a table on another worksheet. Attached is my file that I am working on to further explain where I am at.

To be clear, I need to write a formula that uses the index match function to check both worksheet tabs. For example, if INV00503 is type "Inventory", then it will return the corresponding value from the table on the Inventory tab. If it the type is "Retired" then it will return a value based on the tables in the retired tab.


Thanks in advance.