Replicating Array Function in Numbers

Question

Level 1

8 points

Replicating Array Function in Numbers

I am having a lot of trouble getting a formula worked out. Looking up tutorials I could see how I can do it in Excel but not in Numbers. I want to have 2 sheets, one where I log tasks and another one that works as a search function. So I can ask for all tasks due by a certain date or all tasks from a certain category. I can get close using xlookup, but I if I have it in multiple rows it just returns the same value. I don't know how to express to the formula "you've already found that result, look again and see if theres anything else under these parameters"

MacBook Air 13″, macOS 11.1

Posted on Dec 19, 2020 9:59 PM

Reply

Answer 1

NickApple95 Author

Level 1

8 points

Dec 21, 2020 5:52 PM in response to Barry

This is the main formula.

"""IF(ISBLANK(Inputs::$B$1),IF(ISBLANK(Inputs::$B$2),IF(ISBLANK(Inputs::$B$3),"No Parameters",IF(IF(ISBLANK(D2),FALSE,D2≤Inputs::$B$3),MAX(H$1:H1)+1,"")),IF(ISBLANK(Inputs::$B$3),IF(C2=Inputs::$B$2,MAX(H$1:H1)+1,""),IF(AND(C2=Inputs::$B$2,IF(ISBLANK(D2),FALSE,D2≤Inputs::$B$3)),MAX(H$1:H1)+1,""))),IF(ISBLANK(Inputs::$B$2),IF(ISBLANK(Inputs::$B$3),IF(B2=Inputs::$B$1,MAX(H$1:H1)+1,""),IF(AND(B2=Inputs::$B$1,IF(ISBLANK(D2),FALSE,D2≤Inputs::$B$3)),MAX(H$1:H1)+1,"")),IF(ISBLANK(Inputs::$B$3),IF(AND(B2=Inputs::$B$1,C2=Inputs::$B$2),MAX(H$1:H1)+1,""),IF(AND(B2=Inputs::$B$1,C2=Inputs::$B$2,IF(ISBLANK(D2),FALSE,D2≤Inputs::$B$3)),MAX(H$1:H1)+1,""))))""""

I also made a few way smaller variations to compare some other values like "Is this task in the same week of the year as today?"

IF(F2=$N$3,MAX(K$1:K1)+1,"")

So I have 2 sheets, one is the task list with most of those rows usually hidden. Then I have the results page where I can search by 1,2, or 3 parameters. Then a few extra tables that are automatically pulling tasks due this week, today, ect.

Hope this helps break everything down clearly! Thanks again!

Reply

Answer 2

Barry

Level 9

60,023 points

Dec 21, 2020 4:50 PM in response to NickApple95

Definitely the longest I've seen in a while!

Can you provide a text copy? I'd like to take a closer look at the pieces without having to retype an accurate copy.

Open a new reply, then go to the document and double click the cell containing the formula as it appears above,

Press command-A to select all the content of the Formula editor, then press command-C to put a copy on the Clipboard.

Return to your new reply, click where you want to put the formula, and ppres command-V to paste the text version there.

Regrads,

Barry

Reply

Answer 3

Barry

Level 9

60,023 points

Dec 20, 2020 11:52 PM in response to NickApple95

Getting the formula to index based on one condition can be done by removing the other (and discarding the AND function. Here's the same table with the formula (seen below the tables) revised to index all rows with Dec in the Date column:

For a formula that will produce an index on either one or two conditions, replace each of the comparisons in AND with a pair of conditions wrapped in OR. The Category pair would return true if the category in column B of Table 1 matched the value in B1 of Table 2 OR if cell B2 of Table 2 was empty. The Date pair would be similar, and the two OR statements would become the arguments for the AND statement.

Structure:

IF( ( (A or B) and (CorD) ), then, else)

I'll leave you to play with that for a while. feel free to call back with a solution or with a request for further help.

Regards,

Barry

Reply

Answer 4

Barry

Level 9

60,023 points

Dec 20, 2020 12:46 AM in response to NickApple95

Hi Nick,

XLOOKUP is probably capable of doing the task, but it's new, and my version of Numbers is from before that function was introduced, so I've used INDEX and MATCH.

Your Data table, Table 1 in the example below, needs one more formula, which may be placed in the empty column below the Today label:

The formula, shown below the table, is placed in cell G2 and filled down to the end of that column.

It's purpose is to construct an index of the rows meeting the two conditions set in B1 and B2 of Table 2. It uses an expanding range to determine the current maximum value in the column, and if both conditions are met, adds 1 to that value. If either condition is not met, the formula returns a null string to the cell in that row.

Table 2, which may be placed on a second Sheet, contains a single formula, shown below the tables in the image below.

The formula is entered as shown in the selected cell (Table 2::B5) and filled right to E5 and down to the end of the columns.

The IF statement here is used as a 'switch' to prevent calculation of the INDEX/MATCH core formula after data from the last indexed row has been collected.

In the core part of the formula , MATCH fonds the index value equal to 4 less than 'this row's row number, an return a number indicating that index value positionin the list that is column G. INDEX then returns the value at that position in Column A.

The formula is filled right to column E, and down to the last row of the table, with the results shown.

I assumed from your example that you wanted only the Tasks meeting both the Category and Due date entered on Table 2, and changes the category of Task 12 to make the number of returned tasks match your sample.

Regards,

Barry

Reply

Answer 5

NickApple95 Author

Level 1

8 points

Dec 20, 2020 2:18 PM in response to Barry

Thank you SO MUCH! I have been trying different solutions on this for a few days and I appreciate your creative solution a lot. Even after reading your explanation I think its going to take me a few reads to understand how it functions completely.

Do you have any suggestions on how to alter the formula to return results with one OR two parameters? As in I can get everything back due by a certain date then further filter it with second parameter like it is now. I am going to try figuring out a solution if nothing else to work on my ability, but thought I would ask in case I can't figure it out.

THANK YOU AGIAN!

Reply

Answer 6

NickApple95 Author

Level 1

8 points

Dec 22, 2020 1:15 PM in response to Barry

Hmm, I’m not having luck replicating that issue. First thing I did was write out everything in a notepad to help me keep track of where I was like-

if 1 is blank

True, check if 2 is blank

False, check if 2

ect ect

Then before I added in the actual “search” function to the formula I had the true/false statements return “Search for 1” or “Search for 2&3” to test the functionality before I got into the more complicated bits.

Reply

Answer 7

NickApple95 Author

Level 1

8 points

Dec 21, 2020 12:14 PM in response to Barry

Defiantly the longest formula I've ever used, but I got everything working and I understand it well enough now to change things around as needed. Thanks again for all the help!

Reply

Answer 8

Barry

Level 9

60,023 points

Dec 22, 2020 1:39 AM in response to NickApple95

Thanks for the text version. Had a chance to scan through it, and take a closer look at the first line,:

IF(ISBLANK(Inputs::$B$1),IF(ISBLANK(Inputs::$B$2),IF(ISBLANK(Inputs::$B$3),"No Parameters”

This will produce the “No Parameters” result if B1 AND B2 AND B3 are all empty, but

it will also produce that result if B1 contains a value and B3 is empty.

The syntax for IF is: IF(expression,if-true,if-false)

If there is anything in B, ISBLANK will return 'false', and the first IF will skip the 'if-true' part (which tests the state of B2) and jump straight to the 'if \-false' part.

isblankB1=F, skip,isblankB3=T

If “No Parameters” should be the result only when B1, B2 and B3 are all empty, a better way to test for that condition would be to make the three ISBLANK tests parts of an AND statement:

IF(AND(ISBLANK(B1),ISBLANK(B2),ISBLANK(B3)),"No parameters",if-false)

(Keep the $ operators on the addresses of the cells in the input table—I've skipped them here for convenience, but you'll need the one to preseve the row if you are filling the formula down and the one keeping the column fixed if you're filing the formula right into other columns.

Will try to make some time for another look tomorrow (actually 'today').

Regards,

Barry

IF(AND(ISBLANK(Inputs::$B$2),ISBLANK(Inputs::$B$3),ISBLANK(Inputs::$B$4)),”No Parameters”,

Reply