Solved

# Excel Formula to Get Cell Value in the Same Row

Posted on 2014-04-01
Medium Priority
766 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

LVL 25

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering.  This article seeks by illustration …
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
###### Suggested Courses
Course of the Month5 days, 17 hours left to enroll

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

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