A Doubt In Ms Excel

[contactskn 2]

Dear friends today one of my students asked a doubt to me regarding excel and I am not knowing the answer for it so please help me with some really working answers for the same. The problem is related with Excel and is as follows-There is a table made in Excel and the fields are as follows-Maths ? ? ? ? ? ? ? S.St. ? ? ? ? ? ? ? ? ?Science ? ? ? ? ? ? ? ? Englishthe values for example are as follows89 ? ? ? ? ? ? ? ? ? ?84 ? ? ? ? ? ? ? ? ? ? ?98 ? ? ? ? ? ? ? ? ? ? ? ?68Now first of all I want to find the maximum out of these values, actually I know that it can be accomplished by =max(a2:d2). and the output here will be 98 but actually I want the output as the subject name of the value which is the highest that is in this example Science. Please help me to get this output. Thanking you all in advance.?xl.bmp

Edited December 10, 2009 by contactskn (see edit history)

[shadowx 0]

Vlookup seems to be what you are after:

 

https://answers.yahoo.com/

[anwiii 17]

i don't know the answer off hand, but i want to clarify for others since your english is a little broken. what you want in your example is not to output "98", but "science". correct? my excel skills have been lost 20 years ago. problem is, this is a spread sheet and not a database. the only way you can relate the #98 to is by the exact location of that number. in this case, the field "c2" then you have to relate c2 to the exact location of the subject which would be c1
so when the fields aren't relative, you just have to exchange the fields somehow where the value of c2 is hidden somehow.
so the only way i can see it being done is having science=c2 and having the output show whatever c2 is equal to.

i remember 25 years ago i was self taught in excel. i also aced it in college and i don't ever recall running in to a problem like this. it has me curious if it can be done....

Dear friends today one of my students asked a doubt to me regarding excel and I am not knowing the answer for it so please help me with some really working answers for the same. The problem is related with Excel and is as follows-There is a table made in Excel and the fields are as follows-

Maths               S.St.                  Science                 English

the values for example are as follows
89                    84                      98                        68

Now first of all I want to find the maximum out of these values, actually I know that it can be accomplished by =max(a2:d2). and the output here will be 98 but actually I want the output as the subject name of the value which is the highest that is in this example Science. Please help me to get this output. Thanking you all in advance. 
xl.bmp

[contactskn 2]

Vlookup seems to be what you are after:
https://answers.yahoo.com/

Dear friend as per your suggestion I have tried Vlookup but unfortunately not working then here I have tried hlookup and it is working for my example but not completely, What I mean to say is by using hlookup I can show the subject name but the subject names should be given below the values. Is there any ways to show the subject names which is written above the values that is in the second row. So please find the attachment in which the formula used is displayed clearly, now I would like to know what should be the change made in the formula so that the needful could be accomplished. That is the subject names written in the second row be displayed and not that in the fourth row. 

xl.bmp

[shadowx 0]

I notice the formula shows A3:D4 which i think should be A3:D3 not 4. Might be worth trying correcting that.What happens if you simply delete the subjects under the numbers?

[anwiii 17]

i must have a weird version of excel because i can't seem to do some things i should be able to do. anyway, when using vlookup, you are referencing a table. in this case subject and grade(or grade percentage) so the formula should look something like =vlookup(max(xx:xx),xx:xx,1)
lookup, table, column#
number

so going through the formula, you essentially looking up a # which in this case is the max value of a particular cell. then you input the cells where the table is which is the subject and the grades. then you enter the comumn to reference. what i did however, was put the table(subject and grades) in columns a&b instead of using rows 1&2. now it seems as though the forumula only work if the #'s in the table are referenced first, before the subjects. now this is using vlookup. i am assuming hlookup would be how you had the original table setup because vlookup wont work with that since the last # in the formula is a row, not a column.

the problem i am having when testing it out is that the formula is not recognizing the max value within the hlookup and is always referencing the last subject in the table. i can add and take away subjects and scores and it will still be the last subject. this has got me so boggled why it works only when the table is reversed.

Dear friend as per your suggestion I have tried Vlookup but unfortunately not working then here I have tried hlookup and it is working for my example but not completely, What I mean to say is by using hlookup I can show the subject name but the subject names should be given below the values. Is there any ways to show the subject names which is written above the values that is in the second row. So please find the attachment in which the formula used is displayed clearly, now I would like to know what should be the change made in the formula so that the needful could be accomplished. That is the subject names written in the second row be displayed and not that in the fourth row. 
xl.bmp

[contactskn 2]

I notice the formula shows A3:D4 which i think should be A3:D3 not 4. Might be worth trying correcting that.
What happens if you simply delete the subjects under the numbers?

Dear friend if I am changing it to d3 then it will give the following result that is it will show an error and if we change the last figure from 2 to 1 then it will only show the greatest value that is 99.
untitled.bmp

[anwiii 17]

go to the excel forum and post your problem. you might get a quicker answer that way

Dear friend if I am changing it to d3 then it will give the following result that is it will show an error and if we change the last figure from 2 to 1 then it will only show the greatest value that is 99.untitled.bmp

[contactskn 2]

i must have a weird version of excel because i can't seem to do some things i should be able to do. anyway, when using vlookup, you are referencing a table. in this case subject and grade(or grade percentage) so the formula should look something like =vlookup(max(xx:xx),xx:xx,1)lookup, table, column#
number

so going through the formula, you essentially looking up a # which in this case is the max value of a particular cell. then you input the cells where the table is which is the subject and the grades. then you enter the comumn to reference. what i did however, was put the table(subject and grades) in columns a&b instead of using rows 1&2. now it seems as though the forumula only work if the #'s in the table are referenced first, before the subjects. now this is using vlookup. i am assuming hlookup would be how you had the original table setup because vlookup wont work with that since the last # in the formula is a row, not a column.

the problem i am having when testing it out is that the formula is not recognizing the max value within the hlookup and is always referencing the last subject in the table. i can add and take away subjects and scores and it will still be the last subject. this has got me so boggled why it works only when the table is reversed.

Dear friend of mine by applying your suggestion I am getting the first cell value in the range and not the actual answer which I am in need. Because I think vlookup as it is used for vertical lookup so its checking the range vertically and due to which this value is shown that is the first value in the series.



untitled.bmp

[shadowx 0]

Sorry you will need to use HLOOKUP instead of VLOOKUP (if it exists, if HLOOKUP doesnt exist then you will need to re-arrange the data so it is vertical)

 

Your formula is slightly wrong though.

 

You have

=VLOOKUP(MAX(A2:D2), A2:D2, 1)

Where your numbers are in the cells A2:D2 and your subjects are in A1:D1 (in the second example_

 

The formula basically says this:

 

vertical look up(for the biggest number in cells(A2:D2), Show me the values from the cell range, A2:D2, 1)

I dont know what the 1 at the end is for

 

So basically you need to make the formula look in the number cells fo rhte highest value and then pick the corresponding subject from the cells ABOVE:

 

Vertical lookup(Biggest number in(NUMBER:CELLS), Show me the corresponding value from, SUBJECT:CELLS, 1)

 

EG:

 

=HLOOKUP(MAX(A2:*D2), A1:*D1, 1)

Try that In your second exmaple where the numbers are on the second row and the subjects on the first. If you copy and paste it then be aware i have put a * between the colon and the D's as otherwise it gets lumped together as the emoticon. So if you copy and paste remember to take out the * Edited December 11, 2009 by shadowx (see edit history)

[contactskn 2]

Sorry you will need to use HLOOKUP instead of VLOOKUP (if it exists, if HLOOKUP doesnt exist then you will need to re-arrange the data so it is vertical)
Your formula is slightly wrong though.

You have


Where your numbers are in the cells A2:D2 and your subjects are in A1:D1 (in the second example_

The formula basically says this:



I dont know what the 1 at the end is for

So basically you need to make the formula look in the number cells fo rhte highest value and then pick the corresponding subject from the cells ABOVE:

Vertical lookup(Biggest number in(NUMBER:CELLS), Show me the corresponding value from, SUBJECT:CELLS, 1)

EG:



Try that In your second exmaple where the numbers are on the second row and the subjects on the first. If you copy and paste it then be aware i have put a * between the colon and the D's as otherwise it gets lumped together as the emoticon. So if you copy and paste remember to take out the *

Dear friend I have tried your suggestion right now but unfortunately its giving #N/A error as shown in the attachment and still I am not able to solve this problem.

untitled.bmp
Edited December 11, 2009 by contactskn (see edit history)

[anwiii 17]

have you tested that? because that looks to be totally wrong information there. the first part is correct where it finds the max value, but when you are searching for that value, you're never going to find the # because you have it searching for the # within the subject.btw- the "1" is used to choose the value for the #. it would be the column the outputted value comes from. but we are dealing with rows here. not comumns so vlookup is out of the question anyway...

[shadowx 0]

I'm no excel expert. I actually dislike most office products, they are just a pain! Anyway:

 

=VLOOKUP(MAX(B:,B:C,2,FALSE)

Is apparently what you need to use where the first cell range is the NUMBER cells to look up and the second cell range is the two corner cells to use. So that's the top left cell which contains the first subject and then bottom right cell that contains the last number. Basically you are marking out a table in the data. You then need to use "1" at the end to tell excel to look in the first column/row of data. You should easily be able to change the V to a H and use HLOOKUP instead.

 

to clarify:

 

The bold text is the cells which contain the numbers to check for the highest value

The Italic text is the table corners. Top left and bottom right of the data.

The underlined text is the row/column to get the value from. This needs to be the row or column where your subjects are. So as it stands this would be row 1 (using HLOOKUP) If the subjects were at the bottom it would be row 2.

 

Does that help?

 

Original source: http://dmcritchie.mvps.org/excel/vlookup.htm

 

(PS the N/A error means excel cant find the term you are searching for or it found empty cells in the table.)

[anwiii 17]

he doesn't want the subjects at the bottom. he actually already solved the problem with the subjects and scores reversed(subjects on the bottom). look at his original table and you will see the subject on TOP.

(using HLOOKUP) If the subjects were at the bottom it would be row 2.

anyway, i just realized something. i was messing around with the fuctions in my spare time and i realized something which now limits my help. microsoft excel is different than microsoft office spreadsheet! i completely forgot and it just now occured to me. are you using excel or ms office spreadsheet? i was trying to figure out an alternative to vlookup and i think the same could have been accomplished with an index and match function...although it's a longer process and you would have to print out the max value along with the subject. unfortunately, i don't have access to the match function. i could always import it, but i don't think i'm gonna waste my time anymore on this when the answer can easily be gotten off the excel forums.

now i'm thinking that maybe certain functions work differently in excel and ms office spreadsheet and that's why hlookup or vlookup isn't working properly and not doing what it's supposed to. i know that in microsoft office spreadsheet, it wont let me inter a true or false value(1 or 2) in the fuctions where normally, excel should allow you to do that. based on this, i know that certain fuctions DON'T work the same(comparing both programs)

good luck with it. i am done sorry i couldn't be of more help. i really did try to find the answer for ya....

[buxgoddess 1]

There are quite a lot of reference on vlookup and hlookup but I am yet to be able to get the answer right.I am using excel 2007 and have not been able to do it so far.The max value is comming correct but the column name does not seem to come right. It gave me null error. Why?