How do I use the script editor to search a string in one cell, and return all results from another sheet?

For Example:

I have my string in cell B1 - which is the item number Data to be searched in a different sheet called "Open PO" Col1 in the data is PO#, Col2 in the data is item number, which should be where to search my string.

How do I create a script to search for all PO's and show them in a list that contains the item number in B1?

  • share a copy of your sheet with an example of the desired output
    – user0
    Commented Jun 6, 2019 at 18:30
  • This question was bumped by Community User. I rolled back it to a revision previous to the tag editing because the tags were added based on an answer by the same user who posted such an answer which after many Community User bumps has not gotten any upvotes and any comments. Commented Oct 15, 2023 at 16:31
  • The question needs more focus because the answer might require repeating the comment of several official docs and explaining programming with JavaScript and including use of the Spreadsheet Service and concepts related to spreadsheets. It also should include more detail directly in the question body, like describing the relevant parts of the spreadsheet, including sample data before and after the script is executed. If the data will not be modified, it should be clear what should be the result of the script execution. Commented Oct 15, 2023 at 16:35

1 Answer 1

  • you can either use VLOOKUP or QUERY - depending on the extent

=IFERROR(VLOOKUP(B1, {'Open PO'!A:B; 'Open PO'!B:B, 'Open PO'!A:A}, 2, 0))

 "select Col1 where Col2 contains '"&TO_TEXT(B1)&"'", 0))

Not the answer you're looking for? Browse other questions tagged or ask your own question.