LinkBack URL
About LinkBacks
Is there a formula to pull the highest, second highest, third highest etc value from a range of cells
for instance, if I wanted the highest value between A1 and A100 to show up in A102
Is there a formula to pull the highest, second highest, third highest etc value from a range of cells
for instance, if I wanted the highest value between A1 and A100 to show up in A102
try the large function
large(range,1) change to 2,3,4 etc.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Thank you vlady
Question
what if there is a name adjacent to the value in the range and I want the name next to the highest value?
for instance, the highest value will show up in A20
A10 is the highest value in the range
B10 had the name of the value
what formula can pull B10 into B20?
you can use vlookup,lookup,index/match
something like
=vlookup(large(range,1),A:B,2)
index(B:B,match(large(range,1),.A:A,0))
Pulling the highest value automaticallyPicture 1.png
I dont always explain myself well so I put a picture
what equation would I put in B7 to get 21
what equation would I put in A7 to get cows
try..
B7=large(B1:B5,1)
A7=index(A1:A5,match(B7,B1:B5,0))
btw..maybe next time you can post the real workbook other members can't open pictures.
Hi,
Find the attached with your example and answers.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Will do Vlady, I didn't know other members can't view
Marvin you are a life saver
thank you both for you help. I truly appreciate it, now I can go to sleep!
So everyone just donates their advice? this site doesn't take donations?
MarvinP, if you would be so kind, would you be able to post the equations if I wanted the second highest values in A8 and B8?
I used =Large for the values in B but it doesnt seem to pull the name from column A
See Marvin's sheet updated with 2nd and 3rd highest values too....
Life's a spreadsheet, Excel!
Say thanks, Click *
Try:
=LARGE($B$1:$B$4,2)
in B7 and A7 will work with no changes.
Help With Error.xlsx
Hey Ace and Marvin, I tried to follow this flow with the example and for B8 and B9 I get "#N/A" any ideas on why that's the case?
In A7 use
=INDEX($A$1:$A$4,MATCH(B7,$B$1:$B$4,0))
this will return exact matches
wait never mind, I missed your updates. I believe its all fixed. Thank you!
Hi,
You're missing your match_type parameter: =INDEX($A$1:$A$4,MATCH(B7,$B$1:$B$4,0))
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks