Need help with formula extracting values based on criteria

[jwillits]

So I am trying to find a formula that will pull out unidentified charges from our bank account. I have a list of the known item charges and I would like to create a formula that will list all the items in the description that do not match those charges. So for instance,

KNOWN CHARGES: APPLE, PEAR, GRAPE

BANK STATEMENT:

APPLE 20.00
ORANGE 30.00
PEAR 15.00
GRAPE 5.00
CARROT 2.00

So in this example, the formula I want would return a list that looks like this, as these items arent in our list of known charges.

ORANGE
CARROT

Appreciate any help, thanks.

 

[Peter_SSs]

Try this, copied across and down.

Excel Workbook
	C	D	E	F	G	H	I
1	Known		BANK STATEMENT:			UNKNOWN
2	APPLE		APPLE	20		ORANGE	30
3	PEAR		ORANGE	30		CARROT	2
4	GRAPE		PEAR	15
5			GRAPE	5
6			CARROT	2
7
Unknown

 

[jwillits]

Try this, copied across and down.

Unknown

	C	D	E	F	G	H	I
Known		BANK STATEMENT:			UNKNOWN
APPLE		APPLE		ORANGE
PEAR		ORANGE		CARROT
GRAPE		PEAR
		GRAPE
		CARROT

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]20[/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula H2 =IFERROR(INDEX(E$2:E$6,AGGREGATE(15,6,ROW(E$2:E$6)-ROW(E$2)+1/(COUNTIF($C$2:$C$4,$E$2:$E$6)=0),ROWS(H$2:H2))),"") <tbody> </tbody>

<tbody>
</tbody>

I think I may have needed to explain a bit better, or else I can't figure out a way to get this to work. We are going to be continuously adding to the bank statement column as months go by, so I would like to search the entire column. Also, it will never provide an exact match to the cell, as we only pulled out key words to verify the known charge. For instance, we will have a lot of stuff that looks like this..

APPLE GDE 3453245
APPLE FDS 54322254
PEAR DKK 243242
ORANGE TCP 2534234

To where the known charge is all of the items containing the key words or phrases we have listed, however, not an exact match of the cell.

Not sure if this makes a difference in the formula, but I would appreciate help getting this to work whether the formula needs some small changes or if I am just doing something incorrectly.

 

[Peter_SSs]

So, if you had "ORANGE TCP 2534234" in the Bank Statement column, what do you want pulled out to the 'Unknown" column?
Just "ORANGE" or the whole cell value "ORANGE TCP 2534234"?

 

[jwillits]

whole value of the cell.

 

[Peter_SSs]

Try this.
K1 is empty or holds a 0
K2 copied down at least to the end of the data (If you want, this column could then be hidden)
H2 is copied across and down

Excel Workbook
	C	D	E	F	G	H	I	J	K
1	Known		BANK STATEMENT:			UNKNOWN
2	APPLE		APPLE GDE 3453245	20		ORANGE TCP 2534234	5		0
3	PEAR		ORANGE TCP 2534234	5		CARROT xyz	25		1
4	GRAPE		APPLE FDS 54322254	30					1
5			PEAR DKK 243242	15					1
6									1
7			CARROT xyz	25					2
8			GRAPE ABC 6598	62					2
Unknown