Help with a lookup function (Vlookup, etc) to find grades

Jeff,

Is there a reason not to segment the students by instructor from the beginning? If there is not then I would sort by instructor then duplicate the sheet (or table) and remove the non-A information in one sheet, while removing non-B information from the next, and so on.

Hi Jeff,

Will tihs work?

The index is created by this formula, entered in H2 and filled down:

=G&" "&COUNTIF($G$2:G2,G2)

The smaller table contains two versions of the formula below, entered in the indicated cell, and filled down (and right):

A2: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $H,0)-1,0),"")

B2: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $H,0)-1,COLUMN()+2),"")

Two versions are necessary as the columns from which values are to be transferred are not contiguous.

The first formula is the single column version, used to transfer the names from column A. It is filled down to the bottom row of the small table.

The second is used to transfer the total from column E and percent from column F. Because these columns are adjacent to each other, the fixed value in the first formula ( 0 ) can be replaced with a formula ( COLUMN()+2 ) to allow filling this formula right into column C as well as down to the bottom row of the small table.

The second small table is a duplicate of the first. The second instructor name has been entered in cell A1.

Dexscriptions, syntax, and examples for each of th functions used can be found in the iWork Formulas and Functions User Guide, which may be downloaded via the Help menu in Numbers '09.

Regards,

Barry

Hi j,

When trying to troubleshoot a formula using an error trap, start by stripping the IFERROR part from the formula, so that you can see what error the rest of the formula is returning.

In your table, the index column is column Q, not H. Make sure you have changes that in both formulas.

Your total column is M, not column E. This is reported in column 2 of the smaller tabllem so the column offset needs to be changes from COLUMN()+2 to COLUMN(()+10

Here are the two formulas from the smaller table. Original first, revised version immediately below the original. Changes in bold.

A2 old: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $H,0)-1,0),"")

A2new: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $Q,0)-1,0),"")

B2 old: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $H,0)-1,COLUMN()+2),"")

B2new: =IFERROR(OFFSET(Table 1 :: $A$1,MATCH($A$1&" "&ROW()-1,Table 1 :: $Q,0)-1,COLUMN()+10),"")

New sample tables:

Regards,

Barry