Help request: Complicated Reference formula in Numbers

There is no function that can locate a name in the middle of an array of names. All of the lookup type functions look in a one-dimensional range only (a single column or a single row). You have two choices that I can think of.

First idea:

If you have only a few managers, you can search each column separately to find a match. You only need to know that there is a match, not what row it is on so you can use COUNTIF instead of a lookup. But if it gets to be more than a few, the formula gets long.

Formula in Table 2 column B =IF(COUNTIF(Table 1::A,A)>0,Table 1::A$1,"")&IF(COUNTIF(Table 1::B,A)>0,Table 1::B$1,"")&IF(COUNTIF(Table 1::C,A)>0,Table 1::C$1,"")

Second idea:

Using formulas, transform Table 1 so it has a column for employee names and a column for their manager names. Then you can use XLOOKUP to get your results from this table. Here is one way to do the transformation.

A2 =MOD(ROW()−2,ROWS(Table 1::A)−1)+2

B2 =QUOTIENT(ROW()−2,ROWS(Table 1::A)−1)+1

C2 =INDEX(Table 1::A:C,A2,B2)

D2 =INDEX(Table 1::$1:$1,B2)

fill down with all to complete the table.

If you add new columns to Table 1, do it in the middle not to the far right or left so that the new columns will be automatically included in the range used in the formula in column C

Make sure Table 1-1 is long enough. You may want to include a lot of extra rows for future expansion. Extra rows won't hurt anything, you'll just get error triangles.

For Table 2, the lookup formula is

=XLOOKUP(A2,Table 1-1::C,Table 1-1::D,"",0)

You can cut Table 1-1 and paste it on a different sheet to get it out of sight.

Table 1-1 does the COLUMN part of the SUMPRODUCT function from the Excel formula, and SUMIF is used instead of SUMPRODUCT

SUMIF(Table 1::A:C, A, 'Table 1-1'::A:C)

which is

SUMIF(test-array, condition, sum-values)

Note that the ranges Table 1::A:C and Table 1-1::A:C do not include the header row of the tables when used in this formula. They both are just the "regular" data cells that contain the employee names.

Say your condition is "E2". It will find a match for "E2" in the 1st column, 2nd row of "test-array" and will sum the corresponding value from the 1st column, 2nd row of "sum-values". In this case that value will be 1. There are no other E2's in the "test-array" so the SUMIF result will be 1. Because we made that value be =COLUMN(), it is giving us the column where "E2" was found. Now that we know the column, INDEX is used on the header row of Table 1 to get the manager name.

That is certainly shorter than my series of IF statements.

It has been in the back of my mind that there must be a better way for when there are more than just a handful of managers. Checking each column individually gets long if there are more than a few managers/columns. Something clicked when I came here to read your solution and ponder the Excel solution some more. Below is a method that is relatively easy, especially as compared to my original method of transforming the table. It still requires an extra table but the formula in it is really really simple.

Formula in Table 1-1

=COLUMN()

The managers names in the header of Table 1-1 are not required. They are there only because I copy/pasted Table 1 to create the table and didn't delete them. In fact, the header row is not required at all and can be deleted.

Formula in column B of Table 2 is

=IFS(A="","", COUNTIF(Table 1::A:C,A)>1,"Appears more than once",TRUE,INDEX(Table 1::A$1:C$1,SUMIF(Table 1::A:C,A,'Table 1-1'::A:C)))

The heart of it is INDEX(Table 1::A$1:C$1,SUMIF(Table 1::A:C,A,'Table 1-1'::A:C))

In the full formula, checking for the presence of a name in column A is required because if there is no name the SUMIF will be 0 and INDEX will give an incorrect result (incorrect for our purposes). The COUNTIF is there to check that the employee's name appears only once. If it appears more than once, the SUMIF would sum both of them and give an incorrect result.

Here's a relatively simple solution using CountIFS

Table 1 contains only entered data, a list of the employees managed by each of the three managers.

Table 1-1 has a random list of the 26 employees, entered in column A. (plus a new employee not yet listed under a manager, and a two empty positions.

The formula shown below the tables is entered in B2 of Table 1-1, then filled down to the last row of that column.

The IFS formula consists of three COUNTIF statements, each of which counts the number of cells containing the appearance(s) in one column of Table 1 of the Employee name in 'this row' of column A of Table 1-1.

Assuming each Employee is recorded only once in Table 1, the count will be either zero or one. If the count is 1, the formula returns the manager name in row 1 of that column. If thee count is zero, control is passed to the next countif.

If all three counts return zero, control is passed to the last pair of arguments, 'true' returns true, snd the last argument returns the text "Not found" to the cell containing the formula.

Regards,

Barry

Yes, there is a solution in Excel that uses Excel "array" functions, which are a different way to use some of the standard functions (like SUMPRODUCT) on arrays. Numbers does not have those so the Excel array formula cannot be used (unless you do this project in Excel, or maybe an Excel clone if they support those functions).