Solved

Excel Formula to Get Cell Value in the Same Row

Posted on 2014-04-01
3
655 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 21

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 500 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

776 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