Forum Discussion
Need assistance to formulate a formula
Good day
I would really appreciate some help with a formula. I have a monthly running report which is captured by date. I want to extract a specific row data from the report when the date is filled in. I used =max(line on different sheet). How can I create a formula that will tell me the cell location in the spread sheet that data is from?
1 Reply
If you are looking to find the cell location of a specific value in a row based on a given date, you can use a combination of functions like MATCH and INDEX in Excel. Assuming your dates are in row 1 and your data is in the rows below, here is an example formula:
Let us say your dates are in row 1 (columns A to Z), and your data starts from row 2, and you want to find the cell location for a specific date in cell A1:
=ADDRESS(MATCH(A1, 1:1, 0), MATCH(A1, 1:1, 0))
This formula uses the MATCH function twice:
The first MATCH finds the column number of the specified date in row 1.
The second MATCH finds the row number of the specified date in column A.
The ADDRESS function then converts these row and column numbers into a cell address.
Replace A1 with the cell where you have the date you're looking for. If the date is in a different cell, adjust the reference accordingly.
Keep in mind that this formula assumes there is only one occurrence of the specified date in the row. If there are multiple occurrences, it will return the column and row of the first match.
Please adjust the ranges and references based on your actual data structure. If your data is in a different range or if you have additional criteria, the formula might need further adjustments.
The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
