# look up highest value from a diff spreadsheet

September 23, 2010 at 07:39:40
Specs: Windows XP
 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 mathSuzy 500Spreadsheet 2Suzy math 410Suzy math 500Suzy math 430Suzy math 450

See More: look up highest value from a diff spreadsheet

#1
September 23, 2010 at 08:09:51
 Try this:With student name in cell A1 on sheet 1Student name in cells A1 thru A4 on sheet 2Student grade in cells B1 thru B4 on sheet 2In cell B1 on Sheet 1 enter the formula:=MAX(IF(Sheet2!\$A\$1:\$A\$4=A1,Sheet2!\$B\$1:\$B\$4))MIKEhttp://www.skeptic.com/

Report •

#2
September 23, 2010 at 08:26:21
 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 600 ```Then modify the about formula into an array formula, 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.MIKEhttp://www.skeptic.com/

Report •

#3
September 23, 2010 at 08:59:26
 Thanks for the posts, I should clarifySpreadsheet 1A BName MathSuzy 410Spreadsheet 2A B CName Subject ScoreName Math 410

Report •

Related Solutions

#4
September 23, 2010 at 09:02:49
 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 scoresThen 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.MIKEhttp://www.skeptic.com/

Report •

#5
September 23, 2010 at 09:47:42
 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 English ```On 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 410 ```On sheet 1 Cell C1 enter the Array formula:=MAX(IF(Sheet2!\$A\$1:\$A\$40=A1,IF(Sheet2!\$B\$1:\$B\$40=B1,Sheet2!\$C\$1:\$C\$40)))MIKEhttp://www.skeptic.com/

Report •

#6
September 24, 2010 at 08:16:00
 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! errorOccurs 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 solutionsEntering 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 ENTERSelect 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 constantMake 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 rangeChange 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 functionsMake 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.

Report •

#7
September 24, 2010 at 08:47:59
 Did you enter the formula as an Array formula 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 is probably TEXT.Here is a quick way to convert TEXT numbers into real numbers using the Paste Special command1) 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.MIKEhttp://www.skeptic.com/

Report •

#8
September 24, 2010 at 09:00:14
 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.MIKEhttp://www.skeptic.com/

Report •

#9
September 24, 2010 at 09:18:22
 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.

Report •

#10
September 24, 2010 at 09:37:38
 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.MIKEhttp://www.skeptic.com/

Report •

#11
September 24, 2010 at 09:47:39
 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 SuzySmithandSuzySmithandSuzySmithAre NOT the same, even though they may appear the same.MIKE

Report •

#12
September 24, 2010 at 09:49:16
 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 ```

Report •

#13
September 24, 2010 at 09:50:19
 Both sets of names are pasted from a concatenated list but I'll go back and check that...

Report •

#14
September 24, 2010 at 10:51:56
 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 910 ```And 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 411 ```Then 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.......MIKEhttp://www.skeptic.com/

Report •

#15
September 24, 2010 at 17:45:58
 worked! Thanks!

Report •

#16
September 24, 2010 at 19:44:09

Report •

#17
September 27, 2010 at 07:13:27
 where did you learn this? Are there classes I could take?

Report •

#18
September 27, 2010 at 08:07:03
 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...MIKEhttp://www.skeptic.com/

Report •

#19
October 5, 2010 at 10:13:30
 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 0 ``` is 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/0 ```I 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...

Report •

#20
October 5, 2010 at 13:21:47
 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 the Preview button 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

Report •

#21
October 5, 2010 at 13:54:37
 I played around a bit more, and it works fine for me.BUTDon't forget that these are ARRAY formulas, do not use just ENTER,you MUST use CTRL-SHIFT-ENTERMIKEhttp://www.skeptic.com/

Report •

#22
October 11, 2010 at 07:33:20
 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 ageI'm getting false for my answer. When I did cell to cell instead of an array(?), I got the right answer...

Report •

#23
October 11, 2010 at 13:55:58
 trying to look up data from another sheetThe key word here is LOOKUP, or VLOOKUP or HLOOKUPIf Sheet 1 looks like this:``` A B 1) Name Age 2) Abel Baker ```and 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 24 ```And 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

Report •

#24
October 11, 2010 at 17:47:54
 Noted. thanks again!

Report •

#25
October 11, 2010 at 17:59:01