I have a very large data set, over 60,000 rows for the first 6 weeks of my period. I have a formula that finds the maximum value (MAXIFS with multiple criteria) and I want to know the cell location of the value found because I need the date and time of the occurrence from this row. Is there a way to show the cell location, and then reference that to obtain the other information that I need? I also tried a pivot table, in which I am able to show the Maximum value for each week (which I need for reporting), but I don't know how to show the date for just the Max value found. The maximum value is a duration of time, which appears multiple times in the data for my call center.
My file is attached. I deleted a lot of information to decrease the size of my file. When I go into the Insert Function box on the formula, the formula result is correct, but I get a #VALUE! error instead of that data in the spreadsheet.
I am using Excel 2019 on PC.
I am reporting on the longest RingTime for each week, with additional information that would lead to the long wait, including the date and time that it happened. The formula has been accurately finding the Max time for each week, but I have been having to scroll through the large amount of data to locate the date/time.
Look at this link--> https://www.extendoffice.com/documents/excel/961-excel-identify-cell-address.html
on the use of cell("address" functionality
May I ask why the conten of column B is a formula (="SR911") and just SR911?
Many other cells have formula's where maybe you wanted text.
If you want the contents to be displayed as text format the cells as text
Try:
=LOOKUP(2,1/(Table134[solacom-RingTime]=Q3)/(Table134[solacom-callType]="SR911")/(Table134[Week]=Q2),Table134[Date Time])
