Apple Numbers

Your Initial post:

The 'attached formula' is incomplete, as displayed on Safari:

"Where is it failing? Any thoughts? "

Does it present an error triangle? If so, click the triangle to show the error message. Copy the message and post it in your reply.

Syntax for MATCH: MATCH(search-for, search-where, matching-method)

Search-for:          a specific value to be searched for.

Search-where:         the range of cells, or list of values in which to search.

Matching-method: one of: largest, find value, smallest

Largest: largest value less than or equal to Search-for

Smallest: smallest value greater than or equal to Search-for

Find value: equal to Search-for.

Match returns a number telling the found value's position in the list. If the 'list' is a full column, the number will be the Row number of the row on which the first instance of the Search-for value being sought.

Does your MATCH formula match the syntax of the function? Do the WEEKDAY and EOM parts match the syntax for these two functions? (Their syntax descriptions may be found in the Function Browser.)

your Second post:

Same with: =INDEX(Consultants!$4:$4,1,MATCH(Contracting!$B7,Consultants!$4:$4,0)+1)

Syntax for INDEX: INDEX(range, row-index, column-index, area-index)

range:               the range of cells from which the indexed value is to be retrieved.

row-index:        the row, within that range of rows, containing the value to be retrieved.

column index:  the column within that range of columns, containing the value to be retrieved.

area-index:       (optional) a number indicating which area in a collection of ranges contains

                          the value to be returned. Omitted if a single range is specified.ow and column

Row and Column indexes are always in effect in the formula, and the list separators for these must be included. If no value is stated for the row index or the column index, Numbers assumes a one dimension range and sets the missing value to 1

What is the intended function of the three "!" characters in this formula? Note that this character is NOT used in the syntax description above.

My guess is that you have imported the formula from another application, or have written the formula using instructions from an application other than Numbers.

Can you provide a screen shot of the table referenced by the formula, and including the row and column reference tabs marking the rows and columns of the part of the table in the shot.

Thank you.

Regards,

Barry

In your screen shot, it appears, from the top of the table:

Row 1 contains the word "Contracting" in cell B1, extending into the space of the empty C1. All other cells in this row appear to be empty.

Row 2 appears to be completely empty.

Cells A3:D3 are empty,

Cells in column E of this row and columns to the right of E contain values that are either text strings or are Date and Time values formatted to display only the short monthname, a hyphen and the last two digits of the year. If this is a Date and Time value , the actual content of E3 will be the full D&T string 2021-01-01 12:00:00 AM

Using the value in this cell to search for a date in column B of this or any other table will likely require the 'date' in E3 to be an actual D&T value.

Row 4 contains no data in columns A:D, and a formula (returning an error mesage) in columns to the right of column D.

Row 5 contains the text Contracting in B5, and no other content.

Rows below this, starting with the one displaying only the text January, appear to be on a separate second table.

Does this table contain only dates and data for January, or does it continue past the highlighted row with a new month label and the Friday dates for February, plus a Totals row following that?

If it continues, could the highlighted row include the month name in the cell now showing only "Total"

More later, but must break off for a while.

Regards,

Barry

Are the values 0, 19, 16, 20 in column B of this table or in a different column?

rows 6 and 7 appear to have no content.

A bit more…

If the lower table is complete as shown (January data only),

is named "Jan",

and that you want to retrieve the individual values in the (highlighted) bottom row of that table,

this is one formula that, placed in any cell of a different table, will return the value in column B of the highlighted row

INDEX(Jan::B,ROWS(Jan::B,headers))

Filled to the right, the formula will increment the two references to column B by one letter for each column it is moved to.

If this table continues downward with a similar pattern of as many Friday rows are needed for each month, ROWS will need to be replaced with a MATCH function to get the position of the monthly total row for each month. This would require revising the column A contents of these rows from the current "Total" to "Jan Total" or "January Total" to provide a distinct value for MATCH to find.

The table name,in this scenario would not name a single month, but could (and for the example, will be) named with the year, as "2021"

Formula for any cell of a separate table:

INDEX(2021::B,MATCH("Jan Total",2021::A,0))

Here's an example. The formula shown below the 'summary' table is entered as shown in the selected cell (B2 of the smaller table) then filled right for as many columns as needed.

The formula pays no attention to the dates in column A of 2021except to compare them with the text value MATCH is looking for. In each copy of the formula, MATCH searches column A, finds the same value, and returns the number corresponding to it's position in that column to INDEX.

INDEX then returns the value from that row of successive columns, as named in its copy of the formula.

Fitting the formula to your table will require a bit more detail regarding the structure of the table, and the location of the values you want to collect.

Regards,

Barry