Numbers Formula Help – Extract Bed Numbers for Various work in Each Week

Question

dabakittie Author

Level 1

4 points

Numbers Formula Help – Extract Bed Numbers for Various work in Each Week

I am working on a crop planning sheet in Numbers and need help writing a formula.

I have two tables:

Plot_1 (Left Table) – This shows which beds are being used for direct sowing ("◉ D. Sow") or transplanting ("△ Transplant")on different weeks.
Table 1 (Right Table) – This is my summary table, where I want to automatically list the bed numbers for each work type (Direct Sowing / Transplanting) based on the selected week.

What I Need the Formula to Do:

In the "Beds" column of Table 1, I want to list all bed numbers where "◉ D. Sow" appears for that specific week(e.g., 25032 or 25033).
If no beds have "D. Sow" for that week, it should return blank or "0".
The same logic should apply for "Transplanting" ("△ Transplant").

Example Expected Output:

For week 25032:

"Direct Sowing" should return "28" (since bed 28 has "◉ D. Sow").
"Transplanting" should return "0" (since no beds have "△ Transplant").

For week 25033:

"Direct Sowing" should return "20, 19, 18, 17, 16, 15, 7".
"Transplanting" should return "4, 3, 2, 1".

Formula Attempts & Issues:

I tried using VLOOKUP, COUNTIF, and TEXTJOIN, but they either return all beds or don't filter correctly.
I also tried COUNTIFS + REGEX, but it still didn't list only the correct beds.

Does anyone know the best way to extract the correct bed numbers dynamically when "D. Sow" or "Transplanting" is found in the corresponding week?

Any help would be greatly appreciated! 🙏

MacBook Air 13″, macOS 15.2

Posted on Feb 7, 2025 12:32 AM

Reply

Answer 1

Camelot

Level 9

58,299 points

Feb 7, 2025 12:55 PM in response to dabakittie

This is extraordinarily complex.

All of the lookup() functions will return a single cell reference, not a range, so it's not possible to get a list of Bed numbers via single function.

The only way I can see this working is via an intermediary table that performs a very complex lookup using a combination of multiple MATCH() functions (to find the relevant Bed number by week number), followed by a series of OFFSET() functions to iterate through the list, to populate the table with the matching rows, from which you can extract the bed numbers. It's not trivial, by far (there's a YouTube video here: https://www.youtube.com/watch?v=5cBUIa31AiA that shows it setup in Excel, which should transfer to Numbers reasonably easily, although it only deals with a one-dimensional lookup (e.g. Bed number) and doesn't take account of the week number.

Instead, I'd step back and look to see if you can use Numbers' Filter functions to restrict the list to only show certain values (e.g. 'D. Show' in 25033) from which you extract the data.

It's also possible a pivot table would work, although at first pass I couldn't find the right permutation to get the data you wanted.

Lastly, a scripted solution to find and extract your data could work, although it would need a little coercion. I'd still take this over trying to craft a single formula for this.

Reply

Answer 2

dabakittie Author

Level 1

4 points

Feb 10, 2025 11:28 AM in response to Camelot

Apologies for the late reply, and thank you for taking the time to break this down for me. I really appreciate the detailed explanation and the video reference. It looks like I might need to step back and reconsider my requirements to simplify the system. I will explore the filtering options and possibly a pivot table first before diving into a more complex approach.

Thanks again for your insights!

Reply