Finding if a value is contained in a list

[karenO]

I have one list of all transactions with multiple entries for each transaction


eg

123 Cod £3.75
123 Mineral Water £1.95
123 Coke £1.50
134 Steak £10
134 Toast £1
256 Coffee £2
256 Tea £2


I have another smaller list of the same transactions and I want to show in the first list if the transaction is listed in the smaller list??

I know it cant be difficult but I am struggling

Thanks very much

K

 

[GTO]

If these are in three columns on a worksheet and you are just looking to check existence in the second list, maybe MATCH()

 

[jasonb75]

Assuming that you have 3 columns, all of which must be identical to be counted as a match

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 31px"><COL style="WIDTH: 97px"><COL style="WIDTH: 46px"><COL style="WIDTH: 14px"><COL style="WIDTH: 64px"><COL style="WIDTH: 31px"><COL style="WIDTH: 97px"><COL style="WIDTH: 46px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">123</TD><TD>Cod</TD><TD style="TEXT-ALIGN: right">£3.75</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">123</TD><TD>Cod</TD><TD style="TEXT-ALIGN: right">£3.75</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">123</TD><TD>Mineral Water</TD><TD style="TEXT-ALIGN: right">£1.95</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">123</TD><TD>Mineral Water</TD><TD style="TEXT-ALIGN: right">£1.95</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">123</TD><TD>Coke</TD><TD style="TEXT-ALIGN: right">£1.50</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">123</TD><TD>Steak</TD><TD style="TEXT-ALIGN: right">£10.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">134</TD><TD>Steak</TD><TD style="TEXT-ALIGN: right">£10.00</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">134</TD><TD>Toast</TD><TD style="TEXT-ALIGN: right">£1.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">134</TD><TD>Toast</TD><TD style="TEXT-ALIGN: right">£1.00</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">256</TD><TD>Tea</TD><TD style="TEXT-ALIGN: right">£2.00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">256</TD><TD>Coffee</TD><TD style="TEXT-ALIGN: right">£2.00</TD><TD style="TEXT-ALIGN: right">0</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">256</TD><TD>Tea</TD><TD style="TEXT-ALIGN: right">£2.00</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>=SUMPRODUCT(--($F$2:$F$6=A2),--($G$2:$G$6=B2),--($H$2:$H$6=C2))</TD></TR><TR><TD>D3</TD><TD>=SUMPRODUCT(--($F$2:$F$6=A3),--($G$2:$G$6=B3),--($H$2:$H$6=C3))</TD></TR><TR><TD>D4</TD><TD>=SUMPRODUCT(--($F$2:$F$6=A4),--($G$2:$G$6=B4),--($H$2:$H$6=C4))</TD></TR><TR><TD>D5</TD><TD>=SUMPRODUCT(--($F$2:$F$6=A5),--($G$2:$G$6=B5),--($H$2:$H$6=C5))</TD></TR><TR><TD>D6</TD><TD>=SUMPRODUCT(--($F$2:$F$6=A6),--($G$2:$G$6=B6),--($H$2:$H$6=C6))</TD></TR><TR><TD>D7</TD><TD>=SUMPRODUCT(--($F$2:$F$6=A7),--($G$2:$G$6=B7),--($H$2:$H$6=C7))</TD></TR><TR><TD>D8</TD><TD>=SUMPRODUCT(--($F$2:$F$6=A8),--($G$2:$G$6=B8),--($H$2:$H$6=C8))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Notice that Steak shows 0 (not found) because the it shows 134 in the first list, but 123 in the second.