How do I search two fields for instances where values match?

I have two fields full of product reference numbers that I want to compare for matches.

The first is called Reference . This is a long list of stuff we make, some of which are out of date and out of stock.

The second is called OldReference , which contains just the old items with remaining inventory.

I want to search the two fields for instances where the reference numbers match, and then mark them somehow. I assume in a third field that returns 1 or 0, true or false, yes or no…

Anybody know how to do this using a formula, lookup field, or some combination?

Thanks in advance to anyone with a good idea. :smiley:


Can you annotate a screen shot to highlight a match?

@kuovonne These would all be matches. I went through 589 records and manually matched things using a linked record. I will have to do this again, though… would love to automate as much as possible.

Try creating a formula field like

    {REFERENCE} = {Old Stuff},

If there could be leading/trailing spaces, you can use

    TRIM({REFERENCE}) = TRIM({Old Stuff}),

Then filter for when the formula field has a value.

Note that your two screen shots have different “old” field names: {OldReference} versus {Old Stuff}. You will need to use the proper field name.

Thanks for responding! I’ll give this a try. (And noted, I’ll be sure to use correct field names in the formula).

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.