Solved

Excel Formula to Get Cell Value in the Same Row

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

790 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