Cross Referencing between worksheets

[SloopJohnB]

I am working on a large database for a processing plant. The attached file is a stropped out version for use as an example.

Number 1

On tab "Electrical Droppers" Column Q is the formula =INDEX('Red Meat'!$AD:$AD,MATCH($K3,'Red Meat'!$G:$G,0)) which uses the information from tab "Red Meat" column AD, I would like to use the information from "Chicken" column AD in this column also. Note in column C on the 3 tabs have either CP or RP for Chicken / Red Meat respectively and the Number in Column F is unique.

Number 2

I would like to use the Description from "Chicken" & "Red Meat" column G in "Electrical Droppers" Column K.

I would like the formula in "Chicken" & "Red Meat" column J =IFERROR(INDEX('Electrical Droppers'!$E:$E,MATCH(F23,'Electrical Droppers'!$J:$J,0)),"") explained what IFERROR does.

Thanks John

[mikeTRON]

I cant really follow what you are trying to accomplish with Number 1, but number 2 is very simple. IFERROR(do this, if it fails do this)

So if the Index gives an error then it will enter a "".

[SloopJohnB]

Hi Mike

Number 1,
I would like to combine the information from "Chicken" column AD and "Red Meat" column AD into "Electrical Droppers" column Q. At the monemt I have the formula =INDEX('Red Meat'!$AD:$AD,MATCH($K3,'Red Meat'!$G:$G,0)) which only looks at "Red Meat"

Number 2,

Sorry I mislead the question, I would like to use the information from "Chicken" column G and "Red Meat" column G into "Electrical Droppers" column K

And then I should have said Number 3,

Please explain what IFERROR in the formula "Chicken" & "Red Meat" column J =IFERROR(INDEX('Electrical Droppers'!$E:$E,MATCH(F23,'Electrical Droppers'!$J:$J,0)),"") does?

Thanks John

[FDibbins]

Looking at your data, there is no *link* between the 2 sheets.

What answers are you expecting? please provide some samples and how youy arrived at them

[SloopJohnB]

Hi Ford

Maybe I am not explaining myself correctly.

Number 1

If you open cell in "Electrical Dropper" Column Q i have a formula that takes the data from "Red Meat" Column AD, which is fine in the top 80 rows, after this I would use the data from "Chicken" Column AD. I could change the formula to be Chicken =INDEX('Chicken'!$AD:$AD,MATCH($K81,'Chicken'!$G:$G,0)).

Now I want to combine both formulas so it looks at both the "Chicken' & "Red Meat" something like this????

=INDEX((('Chicken'!$AD:$AD,MATCH($K3,'Chicken'!$G:$G,0))'Red Meat'!$AD:$AD,MATCH($K3,'Red Meat'!$G:$G,0)))

[FDibbins]

I did ask for some sample answers, but try this...
=INDEX(IF(ISNUMBER(SEARCH("chicken",M3,1)),Chicken!$AD:$AD,'Red Meat'!$AD:$AD),MATCH($K3,IF(ISNUMBER(SEARCH("chicken",M3,1)),Chicken!$AD:$AD,'Red Meat'!$G:$G),0))

[SloopJohnB]

Thanks Ford but it don't produce the results I am after.

May be if I looked at from a different perspective, and start again.

In Column Q "Electrical Droppers" the cells search "Chicken" & "Red Meat" column AD for the data, this data then shows in Column Q, also coping across the corresponding data in Chicken & Red Meat Column G into "Electrical Dropper" Column K,

From then on I manually load the information in Columns E & F "Electrical Droppers"