HELP! How to search across multiple columns

Hi markmal,

This may work for you

Using the functions IF AND LEFT.

Formula in Results A2:

IF(AND(LEFT(Authors and Titles::$A2,1)=Conditions::$B$2,LEFT(Authors and Titles::$B2)=Conditions::$B$3),Authors and Titles::A2,"")

If there is no AND match, the formula will return "" (NULL, blank).

The Results table must be longer than the Authors and Titles table. Expect some red error triangles in extra rows. Perhaps another user can show how to stop those error triangles.

You wrote:

P and K are just an example. There can be other conditions.

Simply enter other letters in the Conditions table for starts with...

Happy Numbering!

Ian.

Here's a solution by programmation. It will create a Results table of only the size of the findings.

Copy the text below and paste it in a Script Editor window. Then follow these instructions to always have the script on hand: https://iworkautomation.com/numbers/script-menu.html

To make the script work, enter the search terms in your Conditions table, as you showed. Then fire the script. You can actually leave one or both conditions blank, it will act as a wildcard. You can also use conditions of more than one letter.

tell application "Numbers"

tell active sheet of front document

set sourceTable to table "Authors and Titles"

set conditionsTable to table "Conditions"

set resultsTable to table "Results"

end tell

set theAuthor to formatted value of cell 2 of row 2 of conditionsTable

if theAuthor is missing value then set theAuthor to ""

set theTitle to formatted value of cell 2 of row 3 of conditionsTable

if theTitle is missing value then set theTitle to ""

set findings to my findMatches(sourceTable, 1, theAuthor, 2, theTitle)

set nbResults to the count of items of findings

tell resultsTable

try -- to prevent an error in case there is no result

set row count to nbResults + 1

end try

set value of every cell of cell range of resultsTable to missing value

set value of cell 1 of row 1 to value of cell 1 of row 1 of sourceTable as text

set value of cell 2 of row 1 to value of cell 2 of row 1 of sourceTable as text

if nbResults > 0 then

repeat with i from 1 to count of items in findings

set value of cell 1 of row (i + 1) to item 1 of item i of findings

set value of cell 2 of row (i + 1) to item 2 of item i of findings

end repeat

end if

end tell

end tell

on findMatches(aRange, cell1, criteria1, cell2, criteria2)

set countChar1 to count of criteria1

if countChar1 = 0 then set countChar1 to -1

set countChar2 to count of characters of criteria2

if countChar2 = 0 then set countChar2 to -1

tell application "Numbers"

set aList to {}

repeat with r from 2 to row count of aRange

try

set cellValue1 to formatted value of cell cell1 of row r of aRange as text

set cellValue2 to formatted value of cell cell2 of row r of aRange as text

set compValue1 to characters 1 thru countChar1 of cellValue1 as text

set compValue2 to characters 1 thru countChar2 of cellValue2 as text

if (compValue1 = criteria1 or countChar1 = -1) and (compValue2 = criteria2 or countChar2 = -1) then

set end of aList to {cellValue1, cellValue2}

end if

end try

end repeat

end tell

return aList

end findMatches

You may have 1000 books but only 3 would match the criteria. How would you like to compile them or highlight them?

Is this P and K combination the only one possible or may you want others afterwards?

Formulas to reduce a long list into a short list are quite arduous to build and since we don't know in advance how many matches there are the short list's table must basically have the same number of rows than the long list's table to begin with.

Try to explain in greater details or build a mock-up result table and post a screen capture (shift-command-5) of it here.

Save the screen captures as files (not in the clipboard) and insert with that: