Need formula help

Question

We have a workbook with a sheet for each month named Jan - Dec.&nbsp; on each sheet is the date in column A and column B, C, D have text data and column E has numbers. What i need is a formula that gives me a highest value of column E across all sheets Jan - Dec and the date from column A that the highest value occurred. the results can be displayed in separate cells if that makes it easier.&nbsp; I hope that makes sense. Thank you in advanceDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Text1&nbsp; &nbsp; &nbsp; Text2&nbsp; &nbsp; &nbsp; Text3&nbsp; &nbsp; Amount1/1/24&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;exm1&nbsp; &nbsp; &nbsp; exm2&nbsp; &nbsp; &nbsp; &nbsp;exm3&nbsp; &nbsp; &nbsp; &nbsp;3421/2/24&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;exm1&nbsp; &nbsp; &nbsp; exm2&nbsp; &nbsp; &nbsp; &nbsp;exm3&nbsp; &nbsp; &nbsp; &nbsp;3761/3/24&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;exm1&nbsp; &nbsp; &nbsp; exm3&nbsp; &nbsp; &nbsp; &nbsp;exm3&nbsp; &nbsp; &nbsp; &nbsp;321&nbsp;

hansvogelaar · Answer

In the following, I entered only Jan-Mar. You can easily expand this to encompass all 12 months.
=LET(dates, VSTACK(Jan!A2:A32, Feb!A2:A30, Mar!A2:A32), 
     amounts, VSTACK(Jan!D2:D32, Feb!D2:D30, Mar!D2:D32), 
     max_amount, MAX(amounts), 
     max_date, XLOOKUP(max_amount, amounts, dates), 
     HSTACK(max_amount, max_date))
&nbsp;

patrick2788 · Answer

Stack, sort, and take:=LET(
    dates, VSTACK(Jan:Dec!$A$2:$A$100),
    amt, VSTACK(Jan:Dec!$E$2:$E$100),
    M, SORT(HSTACK(dates, amt), 2, -1),
    TAKE(M, 1)
)The above solution utilizes a 3D reference.Create a 3-D reference to the same cell range on multiple worksheets - Microsoft SupportSince you have a date column, another option might be to consolidate the information in one sheet then the analysis becomes easy and there's no need to stack the data.

Forum Discussion

Need formula help

Share

Resources