Getting Corresponding Cell In Google Sheets?

0
votes

I have a Google sheet for tracking my weight. I have two columns: Date and Weight. While the goal is to have the weight column sorted in descending order, that doesn't always happen in reality...

The data essentially looks like this (weights changed to far lower values, of course):

Date    |Weight 
04/01/10|195 
04/02/10|194 
04/03/10|190 
04/04/10|198

etc.

Anyway, I have a cell in another spot on the sheet that shows the minimum value from the weight column using this formula

=(Min(B:B))

What I would like to do is display the corresponding date cell for whatever the minimum value from the weight column is. So, with this dataset, I want to show 190 for weight and 04/03/10 for date. Is there any way to get that corresponding cell? I looked through the function reference for Google docs, but can't get anything going. I tried using some of the functions from the Lookup category, but got nowhere. Most want a cell reference, but the min() function returns a value. I think I need to somehow get min() to give me a cell reference, but I don't know how to do that. HLOOKUP() sort of seemed like it might be appropriate, but the docs were a bit spotty, and it didn't do anything but error out the cell.

Of course, I may be barking up the wrong tree entirely.

Thoughts?

4
google-sheets
Still monitoring your weight Joey? Do any of the answers fit your question? - Jacob Jan Tuinstra

4 Answers

1
votes

I would use the following two formula's:

  • MIN(B2:B)
  • FILTER(A2:A;B2:B=minimal value)

If there are more results, they need to be included as well.

See example file I've created: Getting Corresponding Cell In Google Docs Spreadsheet?

1
votes

Not barking up the wrong tree, actually very close: 

=index(A:A,match(min(B:B),B:B,0))

should meet your requirement.

Working inside out: min(B:B) (as you had) returns the lowest weight (ie 190) in ColumnB.

match then finds the position of that value relative to the start of the range in which the value is searched for. So assuming Date is in A1, that returns 4, being the fourth row in ColumnB, where 190 is. The 0 in the formula is to ensure that only the position of an exact match is returned.

Now we know we need the content of the fourth row we can go looking for the value there in ColumnA with index, returning 04/03/2010.

Not all is ideal however. It is possible that a weight of 190 was achieved on separate days. It is the nature of match that where an exact match is required and found the function stops looking for any further instances. Hence as things stand 04/03/2010 will be returned for 190 however often that is the weight for a day after 04/04/2010 - unless other steps are taken, such as to delete/remove/ignore data from 04/03/2010.

0
votes

You need to change the order of the column as the search column (the weight should be the first in the search array. Then you can use the VLOOKUP formula:

=VLOOKUP(C7,A:B,2,false)

C7 holds the MIN formula that you used: =(Min(A:A)) - note the column order change

0
votes

one-cell solution to get minimal value with the latest day:

={MIN(B:B), TO_DATE(VLOOKUP(MIN(B:B), SORT({B:B,A:A}, 2, 0), 2, 0))}

0

to get all minimal values with dates:

=QUERY(A:B, "select B,A where B matches '"&MIN(B:B)&"' order by A desc", 0)

0