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

Wayne Contello Austin, Texas
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 nonA information in one sheet, while removing nonB information from the next, and so on.

Like (0)


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

Like (0)


Wayne: I have the students grouped by which course section they are enrolled in (there are 9 sections). I didn't let on that these were laboratory courses that have students from all different instructors. But the lab course does not carry credit  so it is important for me to lump the students together by instructor so he or she can add this lab grade to their lecture component. OTHERWISE, what you stated would be a nice fix

Like (0)


Thanks, Barry  what you have here looks great  now to see if I can implement it myself

Like (0)


Hmmm... I can't seem to apply this to my spreadsheet. Let me post a portion of my actual database table (titled Table 1). Rows 1 and 2 are header rows, names (and grades) begin in row 3. I have the index successfully in place (rightmost column), but when I try to modify what you've placed here, I get only empty cells in the two small tables.
Name
Caffeine
MP & Distillation
Fractional Distillation
Acid/Base Extraction
Prep GC
TLC & Column
MultiStep
Photochem
Dehydration
1Octanol
NADH
Total
%
PTS(50)
Instructor
10
10
10
10
10
10
10
10
10
10
110
Dan
9
10
10
10
10
10
9
9
10
10
97
97%
49
A
A 1
Jacob
10
10
10
10
9
9
9
10
10
10
97
97%
49
B
B 1
Perry
9
10
10
9
10
8
9
9
9
9
92
92%
46
A
A 2
Robert
8
8
10
9
10
10
8
9
9
10
91
91%
46
A
A 3
Laura
10
9
9
10
9
9
9
9
10
10
94
94%
47
B
B 2

Like (0)


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

Like (0)


Awesome  thanks  it works great!

Like (0)
