Search multiple numbers (not text) with OR?

Mark Lappegaard
Mark Lappegaard ✭✭✭✭
edited 11/07/23 in Smartsheet Basics

PROBLEM: Per below, it's possible to use OR to find results containing included words. But it doesn't seem to work for numbers.

BACKGROUND: I have a long list of product numbers from another system. To see if they are in Smartsheet, I copy and paste them into the search box, one at a time. This is boring and slow. I would rather put the whole list into the search box with each product number separated by OR. For example: "00001 OR 0002 OR 0003"

Advice?

Use search to find Smartsheet items | Smartsheet Learning Center

Tags:

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Is your long list able to be pasted into Smartsheet? If so, you could use an IF & MATCH combo to find which are able to be found and which are not with a cross sheet reference to your existing parts listing by using something like this as a column formula:

    =IFERROR(IF(MATCH([Search number column]@row, {Smartsheet part list column}, 0) > 0, "Found"), "Not found")

    Sample data:

    Cross sheet check:

    As you can see, it should work for numbers and text.

    Hope this helps, but if I've misunderstood something or you've any problems/questions then just post! 🙂

  • Mark Lappegaard
    Mark Lappegaard ✭✭✭✭
    edited 11/08/23 Answer ✓

    Wow, @Nick Korna. That is a clever approach. Nice idea. Thanks for that and the well laid out instructions. :)

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Is your long list able to be pasted into Smartsheet? If so, you could use an IF & MATCH combo to find which are able to be found and which are not with a cross sheet reference to your existing parts listing by using something like this as a column formula:

    =IFERROR(IF(MATCH([Search number column]@row, {Smartsheet part list column}, 0) > 0, "Found"), "Not found")

    Sample data:

    Cross sheet check:

    As you can see, it should work for numbers and text.

    Hope this helps, but if I've misunderstood something or you've any problems/questions then just post! 🙂

  • Mark Lappegaard
    Mark Lappegaard ✭✭✭✭
    edited 11/08/23 Answer ✓

    Wow, @Nick Korna. That is a clever approach. Nice idea. Thanks for that and the well laid out instructions. :)