Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Formula to Get Cell Value in the Same Row

Posted on 2014-04-01
3
Medium Priority
?
717 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

721 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