We maintain 2 spreadsheets containing student data. One lists the student, their demographic info and the highest test score achieved. The other lists each score by student. Is it possible to set up a formula in the 1st spreadsheet that would look in the 2nd spreadsheet for the subject and then for the highest value? spreadsheet 1

Name math

Suzy 500Spreadsheet 2

Suzy math 410

Suzy math 500

Suzy math 430

Suzy math 450

Try this: With student name in cell A1 on sheet 1

Student name in cells A1 thru A4 on sheet 2

Student grade in cells B1 thru B4 on sheet 2

In cell B1 on Sheet 1 enter the formula:=MAX(IF(Sheet2!$A$1:$A$4=A1,Sheet2!$B$1:$B$4))

MIKE

If your going to have multiple names on Sheet 1, ie: A 1) Suzy 2) James 3) Edward

and multiple scores on Sheet 2 ie:A B 1) Suzy 410 2) Suzy 500 3) Suzy 430 4) Suzy 600 5) James 410 6) James 500 7) James 430 8) James 600 9) Edward 410 10) Edward 500 11) Edward 430 12) Edward 600Then modify the about formula into an

arrayformula, by pressing CTRL-SHIFT-ENTER.

After entering the formula, do not press ENTER, press CTRL-SHIFT-ENTER, you will see curly brackets surrounding the formula.{=MAX(IF(Sheet2!$A$1:$A$40=A1,Sheet2!$B$1:$B$40))}

Now you can drag the formula down as many rows on sheet 1 as necessary, just make sure the ranges

Sheet2!$A$1:$A$40

and

Sheet2!$B$1:$B$40

which are set for 40 rows, cover the correct number of rows you need.MIKE

Thanks for the posts, I should clarify Spreadsheet 1

A B

Name Math

Suzy 410

Spreadsheet 2

A B C

Name Subject Score

Name Math 410

It is also possible to use a =VLOOKUP and get the results you want. On Sheet 2, sort the entire sheet, highest to lowest on Column B, the test scores

Then on sheet 1 enter the formula:

=VLOOKUP(A1,Sheet2!$A$1:$B$40,2,FALSE)

This works because the Vlookup will stop after it finds the first name match, and since the data is sorted highest to lowest, that persons highest score will be displayed.

The biggest problem is that you have to insure that sheet 2 is always sorted,

while a nice feature of using the array formula, is nothing has to be sorted.And I have way to much time of my hands today.....

Think I'll go cut the lawn.MIKE

OK, try this: On sheet 1:

A B 1) Suzy Math 2) James Math 3) Edward Math 1) Suzy History 2) James History 3) Edward History 1) Suzy English 2) James English 3) Edward EnglishOn sheet 2

A B C 1) Suzy Math 600 2) Suzy English 600 3) Suzy Math 500 4) Suzy History 410 5) Edward Math 900 6) Edward English 600 7) Edward History 500 8) Edward History 900 9) James Math 700 10) James Math 500 11) James English 430 12) James History 410On sheet 1 Cell C1 enter the

Arrayformula:=MAX(IF(Sheet2!$A$1:$A$40=A1,IF(Sheet2!$B$1:$B$40=B1,Sheet2!$C$1:$C$40)))

MIKE

I get a #VALUE! error. Wondering if I applied the formula wrong... =MAX(IF('raw data'!$E$3:$E$693=C2,IF('raw data'!$M$3:$M$693=BJ1,'raw data'!$Q$3:$Q$693)))

Correct a #VALUE! error

Occurs when the wrong type of argument or operand is used.Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears.

Review the possible causes and solutions.

Possible causes and solutions

Entering text when the formula requires a number or a logical value, such as TRUE or FALSEMicrosoft Excel cannot translate the text into the correct data type. Make sure the formula or function is correct for the required operand or argument, and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!.

Entering or editing an array formula, and then pressing ENTER

Select the cell or range of cells that contains the array formula, press F2 to edit the formula, and then press CTRL+SHIFT+ENTER.

Entering a cell reference, a formula, or a function as an array constant

Make sure the array constant is not a cell reference, formula, or function.

Supplying a range to an operator or a function that requires a single value, not a range

Change the range to a single value.

Change the range to include either the same row or the same column that contains the formula.Using a matrix that is not valid in one of the matrix worksheet functions

Make sure the dimensions of the matrix are correct for the matrix arguments.

Running a macro that enters a function that returns #VALUE!

Make sure the function is not using an incorrect argument.

Did you enter the formula as an Arrayformula by pressing CTRL-SHIFT-ENTER.

After entering the formula, do not press ENTER, press CTRL-SHIFT-ENTER, you will see curly brackets surrounding the formula.It should appear like:

{=MAX(IF(Sheet2!$A$1:$A$40=A1,IF(Sheet2!$B$1:$B$40=B1,Sheet2!$C$1:$C$40)))}

{=MAX(IF('raw data'!$E$3:$E$693=C2,IF('raw data'!$M$3:$M$693=BJ1,'raw data'!$Q$3:$Q$693)))}

Also, are you copying any data? If so make sure all your Numbers are actually numbers, and are not being read as text.

A quick and dirty way to tell the difference,

if the number is left justified in the cell it isprobablyTEXT.

Here is a quick way to convert TEXT numbers into real numbers using the Paste Special command

1) In any blank cell, type the value 1.

2) Select the cell in which you typed 1, and then click Copy on the Edit menu.

3) Select the range of cells with the values that you want to convert to numbers.

4) On the Edit menu, click Paste Special.

5) Under Operation, click Multiply. Under Paste, click Values, and then click OK.MIKE

I just checked my Excel work book and am unable to duplicate your error,

even with the numbers on sheet 2 stored as TEXT I do not get a #VALUE error.MIKE

It was the control-shift-enter vs. enter problem. Now it is giving me an answer of 0. It shows 400, 410, 430, but says false... I went to formula audit. It shows every students name = first name on the list but then says it's false... I'll try it with the small group and see if I can get it to work from there... I really appreciate your help with this.

Show what your data really looks like. Post a section of both sheets using the PRE tags found just above the Post Reply box.

Click the PRE symbol and put your example between the tags.Also you can use the Preview Follow Up button on the bottom to check how everything looks and make corrections before you post.

MIKE

If your using First and Last names, and/or two word Subjects, then they have to be exactly the same in both sheets. Spaces COUNT, so that

Suzy<space>Smith

and

Suzy<space><space>Smith

and

<space>Suzy<space>SmithAre NOT the same, even though they may

appearthe same.MIKE

It totally works the way you have it laid out. I figured out my problem... I'm hoping to have sheet 1 read like this Math English History Suzy 600 600 410 James Edward Suzy James Edward Suzy James James Edward

Both sets of names are pasted from a concatenated list but I'll go back and check that...

If you want sheet 1 to look like this: A B C D 1) Student Name Math English History 2) Suzy Smith 600 600 400 3) James Jones 900 430 410 4) Edward Molina 900 600 900 5) Suzy Jones 610 610 410 6) James Molina 910 430 411 7) Edward Jones 910 610 910And Sheet 2 appears as:

A B C 1) Suzy Smith Math 600 2) Suzy Smith English 600 3) Suzy Smith Math 500 4) Suzy Smith History 400 5) Edward Molina Math 900 6) Edward Molina English 600 7) Edward Molina History 500 8) Edward Molina History 900 9) James Jones Math 700 10) James Jones Math 900 11) James Jones English 430 12) James Jones History 410 13) Suzy Jones Math 610 14) Suzy Jones English 610 15) Suzy Jones Math 510 16) Suzy Jones History 410 17) Edward Jones Math 910 18) Edward Jones English 610 19) Edward Jones History 510 20) Edward Jones History 910 21) James Molina Math 710 22) James Molina Math 910 23) James Molina English 430 24) James Molina History 411Then you will need to modify the formulas on sheet 1 as follows:

In Cell B2 enter the ARRAY formula:

=MAX(IF(Sheet2!$A$1:$A$40=A2,IF(Sheet2!$B$1:$B$40=$B$1,Sheet2!$C$1:$C$40)))

In Cell C2 enter the ARRAY formula:

=MAX(IF(Sheet2!$A$1:$A$40=A2,IF(Sheet2!$B$1:$B$40=$C$1,Sheet2!$C$1:$C$40)))

In Cell D2 enter the ARRAY formula:

=MAX(IF(Sheet2!$A$1:$A$40=A2,IF(Sheet2!$B$1:$B$40=$D$1,Sheet2!$C$1:$C$40)))

Remember CTRL-SHIFT-ENTER.......

MIKE

worked! Thanks!

where did you learn this? Are there classes I could take?

Check out your local community college, also your local high school or library may offer night classes. For on-line tutorials, check here:

http://office.microsoft.com/en-us/s...

MIKE

not sure what I did but STUDENT NUMBER concatenate LAST NAME FIRST NAME NAME STUDENT ID PHONE STREET ADDRESS CITY STATE ZIPCODE CURRENT STATUS TRANSPORTATION ATTENDANCE REFERRAL TRANSPORTATION TEACHER W/D DATE REFERRAL SCHOOL "PROGRAM AT ENTRY" "AGE AT ENTRY" "GRADE AT ENTRY" AGE NOW AGE NOW REFERRAL DATE ASSESSMENT DATE ENTRY DATE "PROGRAM CHANGE" DISCIPLINE REFERRAL INFRACTIONS COMMENTS ENTRY TABE READING GATES WRITING SOCIAL STUDIES SCIENCE READING MATH 1 1, Student 1 Student 1, Student 3017043 xxx-xxxx 123 any street RICHMOND VA 23237 WITHDRAWN Y Y C COMMUNITY HS PRE-GED 16 9 18.8 18.8 11/20/2008 11/24/2008 12/15/2008 8.2 0is on the main sheetthe second sheet with data looks like

User ID name Start Year/Start Month/Start Day Instructor Name Class Description Form Subject combine test info Assignment Description test name dif Score Number Correct Number Possible Total Correct Completed Month Completed Day Completed Year Completed Date 46 1, student 2010/05/11 SAKPONOU PM PC MATH Form PC-MATH Form PC - Mathematics Part I 400 4 7 11 0 0 2010 2010/0/0 46 1, student 2010/05/11 SAKPONOU PM PC READING Form PC-READING Form PC - Language Arts, Reading 400 9 20 9 0 0 2010 2010/0/0 46 1, student 2010/05/11 SAKPONOU PM PC SCIENCE Form PC-SCIENCE Form PC - Science 370 9 25 9 0 0 2010 2010/0/0 46 1, student 2010/05/11 SAKPONOU PM PC SOCIAL STUDIES Form PC-SOCIAL STUDIES Form PC - Social Studies 410 10 25 10 0 0 2010 2010/0/0 46 1, student 2010/05/11 SAKPONOU PM PC WRITING Form PC-WRITING Form PC - Language Arts, Writing Part I 430 16 25 2 0 0 2010 2010/0/0I keep ending up with 0 as my answer when the answer for writing on the 1st one should be 430. This may be too much of a mess to look at but if you think you can help...

I tried to import your example into a spreadsheet, but not sure it worked. What column are you using to identify the student? The Name, Student ID or the User ID?

It appears that's probably where your getting mixed up.

The Name and/or ID must be the same on each sheet,

On (my copy) sheet 1 you have the ID as just the number 1,

while on (my copy) sheet 2 your have User ID as 46 ??On (my copy) sheet 1 and 2, the Name appears as 1,Student,

Not sure this is correct.

When you post, use thePreviewbutton on the bottom to look at what you've got.

You can then make changes, and by checking the Preview box above your post, you can look at it again. Just keep making a check in the Preview box, make your adjustments until your satisfied.MIKE

I played around a bit more, and it works fine for me. BUT

Don't forget that these are ARRAY formulas, do not use just ENTER,

youMUSTuse CTRL-SHIFT-ENTERMIKE

New question - same idea - trying to look up data from another sheet =IF(Sheet2!$U$2:$U$160=A2, Sheet2!$X$2:$X$160)

U2:U160 is name, A2 is matching name, X2:X160 is age

I'm getting false for my answer. When I did cell to cell instead of an array(?), I got the right answer...

trying tolook updata from another sheet

The key word here is LOOKUP, or VLOOKUP or HLOOKUPIf Sheet 1 looks like this:

A B 1) Name Age 2) Abel Bakerand Sheet 2 looks like this:

U V W X Name Date Date Age 1) Abel Baker 18 2) Charles Delta 20 3) Edward Francis 22 4) George Inge 24And all you want is to grab the persons AGE, then

=VLOOKUP is what you need.In Sheet 1 cell B2 enter the formula:

=VLOOKUP(A2,Sheet2!U2:X5,4,FALSE)

Just a note: New Question, New Thread.

Helps keep things organized and easy to search.

Thanks.MIKE

Noted. thanks again!

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History