I want to reference a table in one workbook and return a result in a table in another workbook. In Table 1 below, AF9:AF12 will contain the formula I would like help with. I want cell AF9 to ask,
IF E9 (Apples) on Table 1 is present in A9:A14 on Table2
AND IF AF8 (Chris W) on Table 1 is present in F9:F14 on Table2
If so, return "Y", If not return "X"
In my example below, AF11 on Table 1 is the only cell which returns "Y" because both "Grapes" and "Chris W" have been found on the same row in Table 2. AF12 on Table 1, for example, returns "X"
because the formula did not find any rows in Table 2 with "Pears" and "Chris W" on the same row.
Table 1 on [Potential] tab
___________Column E_____Column AF
Row 8______FRUIT_______CHRIS W
Row 9______Apples_______X
Row 10______Bananas______X
Row 11______Grapes_______Y
Row 12______Pears________X
Table 2 on [Accounts] tab
___________Column A_____Column F
Row 8______FRUIT_______NAME
Row 9______Bananas_____Dan W
Row 10______Figs________Dunc M
Row 11______Grapes______Chris W
Row 12______Limes_______Stew F
Row 13______Oranges_____Emily H
Row 14______Pears_______Tim W
Table 1 is in a workbook called 'Poten. SU&Mon_MAS_110327' and a Tab called 'Potential'
Table 2 is in a workbook called 'CasScalp_MAS_110416' and a Tab called 'Accounts'
I have experimented with VLOOKUP and HLOOKUP with no luck. Can anyone help. Thanks.
Dan
IF E9 (Apples) on Table 1 is present in A9:A14 on Table2
AND IF AF8 (Chris W) on Table 1 is present in F9:F14 on Table2
If so, return "Y", If not return "X"
In my example below, AF11 on Table 1 is the only cell which returns "Y" because both "Grapes" and "Chris W" have been found on the same row in Table 2. AF12 on Table 1, for example, returns "X"
because the formula did not find any rows in Table 2 with "Pears" and "Chris W" on the same row.
Table 1 on [Potential] tab
___________Column E_____Column AF
Row 8______FRUIT_______CHRIS W
Row 9______Apples_______X
Row 10______Bananas______X
Row 11______Grapes_______Y
Row 12______Pears________X
Table 2 on [Accounts] tab
___________Column A_____Column F
Row 8______FRUIT_______NAME
Row 9______Bananas_____Dan W
Row 10______Figs________Dunc M
Row 11______Grapes______Chris W
Row 12______Limes_______Stew F
Row 13______Oranges_____Emily H
Row 14______Pears_______Tim W
Table 1 is in a workbook called 'Poten. SU&Mon_MAS_110327' and a Tab called 'Potential'
Table 2 is in a workbook called 'CasScalp_MAS_110416' and a Tab called 'Accounts'
I have experimented with VLOOKUP and HLOOKUP with no luck. Can anyone help. Thanks.
Dan
