VLOOKUP problem

[ddub25]

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

 

[jasonb75]

One way

=IF(SUMPRODUCT(--(Sheet2!$A$9:$A$14=E9),--(Sheet2!$F$9:$F$14=$AF$8)),"Y","X")

You will need to redirect Sheet2! to your accounts tab.

 

[ddub25]

=IF(SUMPRODUCT(--([CasScalp_MAS_110416.xlsx]Acounts!A10:A742=E11),--([CasScalp_MAS_110416.xlsx]Acounts!F10:F742=$AF$8)),"X","Y")

I have adapted the SUMPRODUCT suggestion to the formula above and it works great, thanks.

I would like to add another condition:

That IF C10:C742 = "B" (aswell as E11 matching A10:A742 and $AF$8 matching F10:F742), then return a "B" instead of an "X".

And IF C10:C742 = "S" (aswell as E11 matching A10:A742 and $AF$8 matching F10:F742), then return a "Y" instead of an "X".

Any ideas on including these. Thanks.

Dan

 

[jasonb75]

See what

=VLOOKUP(SUMPRODUCT(([CasScalp_MAS_110416.xlsx]Acounts!A10:A742=E11)*([CasScalp_MAS_110416.xlsx]Acounts!F10:F742=$AF$8),CODE([CasScalp_MAS_110416.xlsx]Acounts!C10:C742&"|")),{0,"X";66,"B";83,"Y"},2,0)

returns, it might need an error handle if there are other possible entries (including blanks) in C10:C742.

 

[ddub25]

=VLOOKUP(SUMPRODUCT(([CasScalp_MAS_110416.xlsx]Acounts!A10:A742=$E11)*([CasScalp_MAS_110416.xlsx]Acounts!F10:F742=$AF$8),CODE([CasScalp_MAS_110416.xlsx]Acounts!C10:C742&"|")),{0,"Y";66,"B";83,"X"},2,0)

I have switched the positions of the "X" and the "Y" at the end of the formula as I want an "X" displayed when the conditons ARE met and a "Y" when the conditions ARE NOT met.

The formula seems to half work. It has correctly displayed a "Y" when the conditions are not met, but when the initial 2 conditions have been matched (A10:A742=$E11 AND F10:F742=$AF$8) it returns #N/A. I assume this is something to do with a conflict in C10:C742 as you thought may happen.

C10:C742 does not contain any blanks, but this column does contain either a "B", "C", "OBS", "P", or an "S". I assume these are causing problems. Any ideas. Thanks.

Dan

 

[jasonb75]

Before I start correcting that, is there any risk of more than 1 row matching the original criteria of (--(Sheet2!$A$9:$A$14=E9),--(Sheet2!$F$9:$F$14=$AF$8))

Multiple matches would cause problems.

In addition, will the entries always be in upper case? With using CODE() to detect the character, "B" is not the same as "b".

 

[ddub25]

Yes, there could be multiple rows matching the original criteria.

Yes, all the entries will always be in upper case.

 

[jasonb75]

Dan, assuming the answers above are no duplicates and always upper case.

=VLOOKUP(SUMPRODUCT(([CasScalp_MAS_110416.xlsx]Acounts!A10:A742=E11)*([CasScalp_MAS_110416.xlsx]Acounts!F10:F742=$AF$8),CODE([CasScalp_MAS_110416.xlsx]Acounts!C10:C742)),{0,"Y";66,"B";67,"X";79,"X";80,"X";83,"Y"},2,0)

Results are in the order, No match, B, C, OBS, P, S

edit:- needs to be changed to allow for the duplicates, what if those duplicates have different entries in column C, is there a priority order to determine correct result?

 

[ddub25]

Thanks, that works great apart from the duplicate problem. I tested it with a situation where the initial conditions were met twice and it returned #N/A as you suggested.

So, is it not possible for a SUMPRODUCT to evaluate properly if there are going to be multiple rows where the initial conditions are met?

I initially tried using a VLOOKUP - would this be able to do the job?

 

[jasonb75]

The problem with lookups is the multiple criteria, would adding another column on the Accounts sheet be permissable?