# compare formula to find no match?

#### JWARD

##### Board Regular
this data is in sheet2 , i need to write a formula or macro in sheet1 that will compare sheet2 columns a & c against columns j & k if a match is found do nothing, if no match is found bring back all unmatched values in a or a & c. how would i go about this?
Book1
ABCDEFGHIJKL
195422011/17/04NAM1767022JeffDenker964814YG433212
295592812/09/04YG8555811JeffWard955928YG855581
3956266KEO2706088JeffDenker964986YGAK2101
496456401/19/05YGAK02144JeffDenker964993H721202
596456401/19/05YGAK21444JeffDenker954220NAM176702
696456401/19/05YGAK21733JeffDenker964982CA1587510
796456401/19/05YGAK01433JeffDenker964982CA4995810
8964814YG4332122thanksforyourorder,Donn96498710310145210
9964910PB1544JeffDenker96498710310500210
10964921ST726020100100LOREN924120EAG1
11964921ST726029100100LOREN734837G993
12964921ST726034100100LOREN734837G993
13964921ST726045100100LOREN956266KEO270608
14964921ST726019100100LOREN964924LTR375121
Sheet2

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
If this is a one-shot operation, you could run Advanced Filter with a computed criterion to pull off the non-matching records. The formula for the computed criteria would be something like:

=ISNA(MATCH(A2,J:J,0))

Note that, for the foregoing set up to work, you need to insert a new row at the beginning with distinct labels.

nope this is something i have to compare on a regular basis, and alot more records then are shown in the example. the example gnaga did isnt what i need, the data he brought back on row 1 is located on row 5 j&k, so that is the data i dont want.

Replies
2
Views
229
Replies
1
Views
128
Replies
4
Views
564
Replies
1
Views
321
Replies
3
Views
109

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back