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.

Thank you for your reply. I see your solution works. The only problem I see is the formula is dependent upon the number in which the column in the table resides, which if a new column is inserted in the table (which may happen) the formula will be broken. This is similar to if I used a VLookup formula, which also breaks is a new column is inserted.

What I was hoping to do is incorporate two index/match formulas in one string that produced a result on the Consolidated tab depending upon the Type (Inventory or Retired) referenced in the adjacent column on the Consolidated tab. The vision i had was something like this: (Note: I am using cell D5 as an example as it contains some numbers in the Inventory tab)

If B4="Inventory" then =IFERROR(INDEX(INVENTORY!$D$3:$D$552,MATCH($C5,INVENTORY!$C$3:$C$552,0)),""), Otherwise if B4="Retired" then =IFERROR(INDEX(RETIRED!$D$3:$D$552,MATCH($C5,RETIRED!$C$3:$C$552,0)),"")

Of course I realize my above description is not a formula but hopefully clarifies the direction I was going.

Thanks in advance.

Brilliant. This works perfectly. Thanks so much!