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]

=VLOOKUP(SUMPRODUCT(MAX(([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)

Needs to be array confirmed with Shift Ctrl Enter.

In the event of duplicates, with differing entries in column C, the highest value found will be used. For example, if the the duplicate rows returned "B" and "OBS" then "OBS" would be used, returning "X".

 

[ddub25]

I could add another column, yes.

Are you suggesting adding a column which would add a unique number (let's say) each time it finds a duplicate row? It could number the duplicates 1,2,3...... Then the formula could just look for the initial conditions A10:A742=E11, F10:F742=$AF$8 but also look for a number 1 in the new column. This way it will find a unique row even though there are multiple rows with the initial 2 conditions.

 

[jasonb75]

There are a variety of ways to do it, but that is basically the sort of thing I was looking at. It depends how you want results to show.

For example.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>FRUIT</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>NAME</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Bananas</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Dan W</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Figs</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Dunc M</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Grapes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Chris W</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Limes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Stew F</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Oranges</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Emily H</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Pears</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Tim W</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Grapes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>OBS</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Chris W</TD></TR></TBODY></TABLE>

What would you expect in AF11 on the other sheet (based on your original example).

 

[ddub25]

I would want AF11 to display "B"

"OBS" is another category but is not important.

 

[jasonb75]

In the new column of the accounts sheet.

=A10&F10

Filled from rows 10 to 742 (based on your examples)

Then in AF9

=IF(COUNTIF([CasScalp_MAS_110416.xlsx]Acounts!Z10:Z742,E11&$AF$8),VLOOKUP(INDEX([CasScalp_MAS_110416.xlsx]Acounts!C10:C742,MATCH(E11&$AF$8,[CasScalp_MAS_110416.xlsx]Acounts!Z10:Z742,0)),{66,"B";67,"X";79,"X";80,"X";83,"Y"},2,0),"Y")

The range Z10:Z742 should be changed to refer to the new column.

 

[ddub25]

=IF(COUNTIF([CasScalp_MAS_110416.xlsx]Acounts!$AC$10:$AC$726,$E20&AF$8),VLOOKUP(INDEX([CasScalp_MAS_110416.xlsx]Acounts!$C$10:$C$726,MATCH($E20&AF$8,[CasScalp_MAS_110416.xlsx]Acounts!$AC$10:$AC$726,0)),{66,"B";67,"X";79,"X";80,"X";83,"Y"},2,0),"X")

I just tried the formula, but it generally returns "#N/A". (Notice i have changed the number of rows on the 'Accounts' sheet to 726 and held some rows or columns with '$' symbol. Column AC is my new column and in the example above it is displaying "GrapesChris W").

The above formula is in a cell that would be evaluating for "Grapes" in Column A and "Chris W" in Column F. In this example it would find them and also find a "C" in Column C and should therefore return an "X". It is actually returning "#N/A".

When I delete "Chris W" from the Accounts sheet, an "X" is displayed, which is the opposite of what should display. If I put "Chris W" back in Column F and change the entry in Column C to either C, B, S, OBS, P, the result is always "#N/A".

 

[jasonb75]

Dan, I just noticed that I forgot to chage the lookup array, it was still searching on the CODE() used in the original formula. In addition, I had it set to "X" not "Y" if there was no match, this should correct it now.

=IF(COUNTIF([CasScalp_MAS_110416.xlsx]Acounts!$AC$10:$AC$726,$E20&AF$8),VLOOKUP(INDEX([CasScalp_MAS_110416.xlsx]Acounts!$C$10:$C$726,MATCH($E20&AF$8,[CasScalp_MAS_110416.xlsx]Acounts!$AC$10:$AC$726,0)),{"B","B";"C","X";"OBS","X";"P","X";"S","Y"},2,0),"Y")

 

[ddub25]

=IF(COUNTIF([CasScalp_MAS_110416.xlsx]Acounts!$AC$10:$AC$736,$E20&AF$8),VLOOKUP(INDEX([CasScalp_MAS_110416.xlsx]Acounts!$C$10:$C$736,MATCH($E20&AF$8,[CasScalp_MAS_110416.xlsx]Acounts!$AC$10:$AC$736,0)),{"B","B";"C","X";"OBS","X";"P","X";"S","Y"},2,0),"Y")

Just tried it and it does exactly what I want. Thanks very much, you've been a great help.

I've just thought of another condition I'd like to add if it is not too awkward. I am a bit worried that I have already asked a lot of you. If it's not too much to ask, I'm after:

IF Column D (on this sheet, not the 'Accounts' sheet) CONTAINS either "M", "R", or "DM"

Then,
return "Y" if FIND the initial match and C10:C736 = either "C", "S", "OBS", "P"
return "B" if FIND the initial match and C10:C736 = either "B"
return "SU" if DO NOT FIND the initial match.

IF Column D (on this sheet, not the 'Accounts' sheet) DOES NOT CONTAIN either "M", "R", or "DM", then return the results as from the orginal formula.

Thanks.

 

[jasonb75]

Not tested, just quickly spliced together in the reply window

=IF(COUNTIF([CasScalp_MAS_110416.xlsx]Acounts!$AC$10:$AC$736,$E20&AF$8),VLOOKUP(INDEX([CasScalp_MAS_110416.xlsx]Acounts!$C$10:$C$736,MATCH($E20&AF$8,[CasScalp_MAS_110416.xlsx]Acounts!$AC$10:$AC$736,0)),IF(OR($D20={"M","R","DM"}),{"B","B";"C","Y";"OBS","Y";"P","Y";"S","Y"},{"B","B";"C","X";"OBS","X";"P","X";"S","Y"}),2,0),IF(OR($D20={"M","R","DM"}),"SU","Y"))

 

[ddub25]

Thanks again Jason, the formula works great. You've been a massive help - this will save me a lot of time.

On the 1st page of the post you posted a table. How do you do that on a forum.? My way of posting a table that doesnt get ruined after formatting is very tedious.

Can you suggest any good books or ebooks for learning Excel formulas? Thanks very much.

Dan