0

For a project, I'd like to look through a set of cells for a maximum in one cell, and auto-populate the next cell based on the corresponding date for the maximum.

For example, column A is the date of the data collection, and column B is the data itself.

In cell C1, I use the formula "=MAX(B1:B10)" to find the maximum of my collected data. In D1, I want to auto-populate the date that corresponds with the maximum value that I'm populating C1 with (or to put it simpler, the date on which the maximum was measured).

Is there a quick-and-easy way to do this? I tried an "and" and an "if" function, but I'm not quite sure how to make it work.

2 Answers2

0

You could do it with XLOOKUP() as gns100 says, and it's very straightforward:

=XLOOKUP(MAX(B2:B23),  B2:B23,  A2:A23)

This is something XLOOKUP() was designed for: avoiding all the contortions folks have had to go through to do this.

Of course, use C1 instead of the MAX() function in the formula, unless you only had it calculated in its own cell to use it in C1. In that case, just make it part of the formula itself, like above, and you needn't have it in its own cell. If it is useful or desirable to have shown, do it in C1 and then use C1 as the lookup value.

(What you are needing is called "looking left" in a data range: all lookup functions before XLOOKUP() would only look to the right, so to speak. There must have been half a dozen useful workarounds (contortions), but all had some degree of understanding needed for them.)

I get the impression you are newer to using Excel so I shall go on some about other solutions. But the above is simplest and best.

Some were sort of straightforward for all that (like INDEX/MATCH) once you understood them. Some used the horrifying (in 1997, not in 2021) "volatile" functions of the Excel world. Oh my, a volatile function! But some were seriously contortion filled... but effective and still pretty easy to understand.

Seems a shame to lose track of all those techniques as the techniques themselves can be used in other formulas, other combinations, for other purposes. For instance, there is a method using CHOOSE() that lets you create, in the formula itself, an ad hoc table with the columns rearranged so that you are "looking right" not left. (So to Excel, column B might be the first column, then column A, not the other way around. Then the date is to the right of the values you are searching through and lookups all work.) But it had weird effects in some circumstances and who needs that???

A different way of doing the basic thing CHOOSE() was used for was to use INDEX() to specify the "natural" range, then a value for rows that would get all the rows, and finally use an Array Constant to ask for the columns but in a different order. Array constants are things that look like: {2,1}

The following is a formula that does what the best choice, XLOOKUP() does, and demonstrates the way INDEX() can feed that favorite of all discerning Excel-ites, VLOOKUP():

=VLOOKUP(MAX(B2:B23),  INDEX(A2:B23,  SEQUENCE(ROWS(A2:B23)),  {2,1}),  2,  FALSE)

It is also handy for anyone who doesn't have XLOOKUP() though SEQUENCE() would then surely need replaced but that's an easy one: ROW(2:23) (fair because you know the start and end rows of your data range).

INDEX() gives VLOOKUP() the data with the lookup values as the first column (the "2" says "put column 2 first" and the "1" says "then put column 1 second") and the dates you desire as the second column. So then VLOOKUP() itself is perfectly normal, nothing strange. You use the MAX() part to specify the lookup value from the data itself.

By the way, you can use LARGE() instead to get various returns. Say you want the top five performers in some competition. Or you want the 3rd, 8th, and 17th performers. Whatever you need. Remembering LARGE() and SMALL() along with MAX() and MIN() is very worthwhile.

Jeorje
  • 1
0

In excel 365 you can use FILTER function:

=FILTER(A1:A10,B1:B10=C1)

If there are multiple dates where maximum data was collected, all will be listed.