I am trying to find the order number in a separate database - if the order number is repeated in this separate database I would like it to return all duplicates, i.e. all data that is assigned to the order number. The order numbers on the left are dynamically downloaded from another place and changed.

I stopped dragging the formula: =FILTER($I$28:$K$48;isnumber(search(B28;$I$28:$I$48)))

but if it finds duplicates (i.e. the order number appears several times in a separate database) there is no place to return the data and #REF! pops up

I am trying to find the order number in a separate database - if the order number is repeated in this separate database I would like it to return all duplicates, i.e. all data that is assigned to the order number. The order numbers on the left are dynamically downloaded from another place and changed.

I stopped dragging the formula: =FILTER($I$28:$K$48;isnumber(search(B28;$I$28:$I$48)))

but if it finds duplicates (i.e. the order number appears several times in a separate database) there is no place to return the data and #REF! pops up

• google-sheets
• google-sheets-formula

• Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Mar 21 at 12:03

1 Answer 1

You can use TOROW or JOIN around your filter.

=TOROW(FILTER($I$28:$K$48;ISNUMBER(SEARCH(B28;$I$28:$I$48))))

=JOIN(", ";FILTER($I$28:$K$48;ISNUMBER(SEARCH(B28;$I$28:$I$48))))