INDEX and MATCH, multiple tables HELP!

[Needhamk]

I have several different excel files I use at work during my day.

One one I successfully used an INDEX and MATCH formula to pull information from my table into my cell.

However I would like to duplicate this in another file, however in my other file I have three tables I would like the information to be pulled from into one cell. Is something like this possible? If it makes a difference, the cell would be located on sheet 1 and the three tables would be located on sheet 2. 

[#2]

@Needhamk 

Please provide more detailed information.

[#3]

@Needhamk 

Maybe like in the attached file?

[#4]

@Hans Vogelaar @OliverScheurich

So I receive chemicals for my company. we have a list of chemicals that are hazardous in terms storage. one group needs 3 months expiration, one need 6 months and the last needs 12 months. There is about 40 chemicals on this list that could possibly come in. So in my excel file, I have sheet 1 that is my incoming chemical list (keeps track of date, quantity, location in building) and sheet 2 contains the three tables of the different hazardous chemicals I have to watch for. Right now I have conditional format rules setup that will highlight each of the chemicals if I type them in on sheet 1 (it highlights blue for 3 months, pink for 6 months and orange for 12 months). until Monday I did not realize the INDEX MATCH functions existed. What sparked my question was they are wanting to use my file for incoming packages at other sites. And I was thinking if I could get the INDEX MATCH to work with multiple tables then if the hazardous list changes at any point it will be a lot easier fix over multiple sites than trying to edit it for conditional format rules.

[#5]

@Needhamk 

I think you can use the Reference arrangement of INDEX. It's a bit different in syntax. Here's what one looks like:

=INDEX((Data!A1:A3,Data!C6:C8,Data!D1:D3),1,1,2)

The bolded part being your 3 ranges.  You will receive a #VALUE! error if the tables used are not all on the same sheet.