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
Medium Priority
717 Views
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
``````
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
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

LVL 23

Expert Comment

ID: 39970494
Try this

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

LVL 12

Accepted Solution

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

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

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.
###### Suggested Courses
Course of the Month8 days, 20 hours left to enroll