How do I search a string, and return all values?

Question

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?

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

=ARRAYFORMULA(QUERY(TO_TEXT('Open PO'!A:B), 
 "select Col1 where Col2 contains '"&TO_TEXT(B1)&"'", 0))