=IF(Match(index) - tricky multiple conditions

**Blackhawks** · 05-29-2013, 11:56 PM

I've racked my brain on this and it's driving me nuts. I have two documents, one old and one new. The new list has all current inventory. I need to show what items have been deleted off of the old list; what items have been added; and what items are still on the list. There really isn't a unique identifier so I'm using multiple condition to do this. I used this function =IF(COUNTIFS(OLD!$B$2:$B$3343,NEW!B9,OLD!$C$2:$C$3343,NEW!C9)>0,"Yes","No") and it works up until a point and then everything comes back "no".

I appologize if this doesn't make much sense - it's late. I'll answer any questions. I appreciate any help you can give. Thanks!

**FDibbins** · 05-30-2013, 12:00 AM

Hi and welcome to the forum

It makes perfect sense, but it will be much easier if we could see samples of what you are working with?
I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Blackhawks** · 05-30-2013, 08:46 AM

Sorry, here is a sample. In this problem sample it works just fine. In the actual file it's only the last 300 that get messed up, but it may be worth noting that I had to change the column name for the last 300 or so, because they didn't match for the old work sheet. Thanks!

**Jonmo1** · 05-30-2013, 08:55 AM

There's nothing wrong with the formula.
As you said, in the book you posted it's working as expected.

It doesn't help much to post a sample book that is not experiencing the problem..

**Blackhawks** · 05-30-2013, 10:11 AM

I see your point... Well do you know of other functions that may work? I was thinking of a if(match(index))), but I'm not sure how to go about it. Basically what I'm trying to do is find out which rows had been deleted and added in the new worksheet. Does anyone know of another way of doing this? Again, there is no unique identifier... even with multiple conditions, there are still about 63 duplicates. Any help would be greatly appreciated.

**Jonmo1** · 05-30-2013, 10:20 AM

Again, there's nothing wrong with the formula you're using. It will accomplish what you seek...
Verifying the existance of one pair of values (New!B9 & New!C9) in another pair of lists (Old!B2:B3343 & Old!C2:C3343).