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.