how to find the max value from table 1

Hi Yanni,

Your question, as asked, has a simple solution. Assuming all of the numeric values are in column B of Table 1, the formula below will return the Maximum value in that column (currently 10).

MAX(Table 1::B)

But from the image of Table 2, it appears you want the maximum value associated with each name in column A.

If Table 1 can be sorted, then a descending sort on column B will permit the use of VLOOKUP in Table 2 to search for each name and return the the value in B from the row in which that name first occurs:

Tables 1 and 2 in original order:

Table 1 has been sorted to place the values in column B in descending order. Table 2 now shows the maximum for each name.

Formula in Table 2, cells B2, B3 and B4: VLOOKUP(A,Table 1::A:B,2,FALSE)

A second method, requiring extra columns on Table 1, but requiring no sorting of either table, is shown below.

The same data is used as in the example above, but I have done a random sort of Table 1 on the assumption that the data will not be as nicely ordered as it is in your example.

The extra columns, one for each name, contain only the values associated with that name.

On Table 1, the formula in each of the cells in these three columns copies only the values associated with the name at the top its column.

On Table 2-1, the formula in column B uses MATCH to choose the column from which to collect the values using INDEX, and MAX to choose and display the largest value collected from that column.

Table 1::C2: IF($A=C$1,$B,"")

Fill right to column E. Fill Down to last row of the table.

Table 2::B2: MAX(INDEX(Table 1::$A:$E,,MATCH(A,Table 1::$1:$1,0)))

Fill down to last row containing a name. Filled beyond that row the formula will generate a "couldn't find" error.

To avoid that error, and permit filling the formula past row 4, place it in one of these containers:

IF(A="","",formula)

or

IFERROR(formula,"")

The first 'switches' the formula off until there is an entry in that row of column A, but does not prevent the 'couldn't find' error if there is not a matching entry in row 1 of Table 1.

The second acts as a general error trap. If the formula generates ANY error message, IFERROR returns the text between the quotation marks (here, a text value with no content).

Regards,

Barry