Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

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

Topic Labels: Formulas
953 4
Showing results for 
Search instead for 
Did you mean: 

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:

Screen Shot 2022-03-02 at 11.17.01 AM

4 Replies 4

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.
Screen Shot 2022-03-02 at 6.34.17 PM

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