IF statements combined with VLOOKUP?

[ddub25]

I have 2 seperate tables - one in one workbook and one in another workbook. In Table 1, 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

 

[iliace]

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"

So something like this:

=IF(AND(ISNUMBER(MATCH(Potential!E9, Accounts!$A$9:$A$14, 0)), ISNUMBER(MATCH(Potential!AF8, Accounts!$F$9:$F$14 0)), "X", "Y")

I'm entering the sheet names by hand, whereas with the actual tables you can click to get the corresponding reference.