Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Formula to Get Cell Value in the Same Row

Posted on 2014-04-01
3
Medium Priority
?
738 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 23

Expert Comment

by:Ejgil Hedegaard
ID: 39970494
Try this

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

Accepted Solution

by:
Harry Lee earned 2000 total points
ID: 39970502
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
ID: 39970642
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

824 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