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

[Admin_Charriol]

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. :grinning_face_with_big_eyes:

-Frank

[kuovonne]

Can you annotate a screen shot to highlight a match?

[Admin_Charriol]

@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.

[kuovonne]

Try creating a formula field like

IF(
    {REFERENCE} = {Old Stuff},
    "MATCH!"
)

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

IF(
    TRIM({REFERENCE}) = TRIM({Old Stuff}),
    "MATCH!"
)

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.

[Admin_Charriol]

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