| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
look for text not using vlookup and sum if
Dear Helpers,
I have a long listing below for checking Name Score Peter 70 Anna 120 Sam 50 ........ I use Max to find the highest score is 120 and I want to use the highest score to look for the name who gets the highest score. How to do so, thanks a lot for your help. Fanny |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
look for text not using vlookup and sum if
=OFFSET(A1,MATCH(MAX(B:B),B:B)-1,0)
-- Gary''s Student - gsnu200836 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
look for text not using vlookup and sum if
Assuming your MAX formula is in C2
place this classic formula in D2 =INDEX(A2:A10,MATCH(C2,B2:B10,0)) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Fanny" wrote: Dear Helpers, I have a long listing below for checking Name Score Peter 70 Anna 120 Sam 50 ....... I use Max to find the highest score is 120 and I want to use the highest score to look for the name who gets the highest score. How to do so, thanks a lot for your help. Fanny |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
look for text not using vlookup and sum if
you may try this without using a separate cell for the MAX formula in C2
described below =INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,0)) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "francis" wrote: Assuming your MAX formula is in C2 place this classic formula in D2 =INDEX(A2:A10,MATCH(C2,B2:B10,0)) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis "Fanny" wrote: Dear Helpers, I have a long listing below for checking Name Score Peter 70 Anna 120 Sam 50 ....... I use Max to find the highest score is 120 and I want to use the highest score to look for the name who gets the highest score. How to do so, thanks a lot for your help. Fanny |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
look for text not using vlookup and sum if
Hi,
If you have titles in the top row then change GS's to =OFFSET(A1,MATCH(MAX(B:B),B:B,)-1,0) or shorten the other suggestion to =INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10)) or try this instead: =LOOKUP(MAX(B2:B9),B2:B9,A2:A9) If you have a tie for the MAX these formula will give different results. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Fanny" wrote: Dear Helpers, I have a long listing below for checking Name Score Peter 70 Anna 120 Sam 50 ....... I use Max to find the highest score is 120 and I want to use the highest score to look for the name who gets the highest score. How to do so, thanks a lot for your help. Fanny |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
look for text not using vlookup and sum if
or shorten the other suggestion to
=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10)) or try this instead: =LOOKUP(MAX(B2:B9),B2:B9,A2:A9) And both of those will fail if the data was like this: Name Score Anna 120 Peter 70 Sam 50 It's just "dumb luck" that they work on the sample as posted. Quality trumps quantity. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, If you have titles in the top row then change GS's to =OFFSET(A1,MATCH(MAX(B:B),B:B,)-1,0) or shorten the other suggestion to =INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10)) or try this instead: =LOOKUP(MAX(B2:B9),B2:B9,A2:A9) If you have a tie for the MAX these formula will give different results. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Fanny" wrote: Dear Helpers, I have a long listing below for checking Name Score Peter 70 Anna 120 Sam 50 ....... I use Max to find the highest score is 120 and I want to use the highest score to look for the name who gets the highest score. How to do so, thanks a lot for your help. Fanny |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
look for text not using vlookup and sum if
Dear Helpers,
Thank you very much for your advice and I have solved my problem. Fanny "T. Valko" wrote: or shorten the other suggestion to =INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10)) or try this instead: =LOOKUP(MAX(B2:B9),B2:B9,A2:A9) And both of those will fail if the data was like this: Name Score Anna 120 Peter 70 Sam 50 It's just "dumb luck" that they work on the sample as posted. Quality trumps quantity. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, If you have titles in the top row then change GS's to =OFFSET(A1,MATCH(MAX(B:B),B:B,)-1,0) or shorten the other suggestion to =INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10)) or try this instead: =LOOKUP(MAX(B2:B9),B2:B9,A2:A9) If you have a tie for the MAX these formula will give different results. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Fanny" wrote: Dear Helpers, I have a long listing below for checking Name Score Peter 70 Anna 120 Sam 50 ....... I use Max to find the highest score is 120 and I want to use the highest score to look for the name who gets the highest score. How to do so, thanks a lot for your help. Fanny |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP (with text) | Excel Discussion (Misc queries) | |||
| Using VLOOKUP and text | Excel Discussion (Misc queries) | |||
| Vlookup using text | Excel Worksheet Functions | |||
| Vlookup text values | Excel Worksheet Functions | |||
| Vlookup a value and text | Excel Discussion (Misc queries) | |||
Linear Mode
