Solved

Excel Formula to Get Cell Value in the Same Row

Posted on 2014-04-01
3
652 Views
Last Modified: 2014-04-01
I have a spreadsheet that contains a date in column A and a number value in column C.

I have a formula that tells me the highest and lowest values in column C. What I would like is to have another formula that tells me the value in A that is on the same row as the highest and lowest values in C.

So if this is my sheet, the date is Column A; the value is column C:
Jan-2014     46
Feb-2014     29
Mar-2014     31
Apr-2014     33

Open in new window

One formula that I already have in place tells me that 46 is the highest value of those four lines. It displays that in cell in another part of the sheet. I even found a formula that tells me what row that is (though I'm not sure that's helpful in my case).

I would like to place a formula in another cell that tells me the value of A1 (Jan-2014) based on C1 being the highest value in C. If things change and C4 ends up being a higher number, it should automatically switch to the value of A4 (Apr-2014).

I'm not sure I'm making a lot of sense here; I hope I'm being clear. Please ask questions if clarification is needed.
0
Comment
Question by:Jono Martin
3 Comments
 
LVL 20

Expert Comment

by:Ejgil Hedegaard
Comment Utility
Try this

=INDEX(A:A,MATCH(MAX(C:C),C:C,0),1)
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
Comment Utility
Well, let's say, your D1 is where the Min is sitting, and E1 is where you want the looked up of column A is sitting (Min),
Column F1 is where the max is sitting, and G1 is where you want to looked up of column A is sitting (Max)

You can do something like this. in E1,
=text(index($A:$A,match(D1,$C:$C,0)),"MMM-YYYY")
In G1,
=text(index($A:$A,match(F1,$C:$C,0)),"MMM-YYYY")

OR

in E1, =text(index($A:$A,match(Min($C:$C),$C:$C,0)),"MMM-YYYY")
in G1, =text(index($A:$A,match(Max($C:$C),$C:$C,0)),"MMM-YYYY")

OR something like this to combine the results in one single cell,

="The Lowest value in Column C is "&MIN($B:$B)&", and it's referenced to "&TEXT(INDEX($A:$A,MATCH(MIN($B:$B),$B:$B,0)),"MMM-YYYY")

="The highest value in Column C is "&MAX($B:$B)&", and it's referenced to "&TEXT(INDEX($A:$A,MATCH(MAX($B:$B),$B:$B,0)),"MMM-YYYY")
0
 

Author Closing Comment

by:Jono Martin
Comment Utility
Excellent. That was easier than I thought it would be. I chose to use the 1st set of solutions you provided and it works perfectly.

Thanks!
Jono
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now