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 math
Suzy 500

Spreadsheet 2
Suzy math 410
Suzy math 500
Suzy math 430
Suzy math 450


See More: look up highest value from a diff spreadsheet

Report •


#1
September 23, 2010 at 08:09:51
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

http://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.

MIKE

http://www.skeptic.com/


Report •

#3
September 23, 2010 at 08:59:26
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


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 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

http://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)))

MIKE

http://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! 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 FALSE

Microsoft 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.


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 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

http://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.

MIKE

http://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.

MIKE

http://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

Suzy<space>Smith
and
Suzy<space><space>Smith
and
<space>Suzy<space>Smith

Are NOT the same, even though they may appear the same.

MIKE

http://www.skeptic.com/


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.......

MIKE

http://www.skeptic.com/


Report •

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

Report •

#16
September 24, 2010 at 19:44:09
Glad I could help.

MIKE

http://www.skeptic.com/


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...

MIKE

http://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 sheet

the 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

http://www.skeptic.com/


Report •

#21
October 5, 2010 at 13:54:37
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,
you MUST use CTRL-SHIFT-ENTER

MIKE

http://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 age
I'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 sheet
The key word here is LOOKUP, or VLOOKUP or HLOOKUP

If 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

http://www.skeptic.com/


Report •

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

Report •

#25
October 11, 2010 at 17:59:01
Glad to help.

MIKE

http://www.skeptic.com/


Report •


Ask Question