INDEX MATCH - replacing the fixed values with fluid formulas

[audiberry]

7H3uWiW.png

Hello, I wish to have the formulas in the above image:

1. Search for the most recent entry.
2. Find the lowest value in the row.
3. Report that value and the header.

I've been unable to get the formula to report the result without using a fixed column and row value. Am I using the right formulas? Thank you! Much appreciated folks.

[hrlngrv]

Are the values in col A presumably from row 2 down to row 703 in ascending order? If so, then the last nonblank row would be the most recent entry, and the lowest value from column B to column W would be given by

A709: =MIN(INDEX(B$2:W$703,MATCH(1,0/ISBLANK(A$2:A$703)),0))

and the corresponding column label by

A710: =INDEX(B$1:W$1,MATCH(A709,INDEX(B$2:W$703,MATCH(1,0/ISBLANK(A$2:A$703)),0))

If A2:A703 is unsorted, it's more complicated.

A709: =MIN(INDEX(B$2:W$703,MATCH(MAX(A$2:A$703),A$2:A$703,0),0))

A710: =INDEX(B$1:W$1,MATCH(A709,INDEX(B$2:W$703,MATCH(MAX(A$2:A$703),A$2:A$703,0),0))

[audiberry]

Are the values in col A presumably from row 2 down to row 703 in ascending order? If so, then the last nonblank row would be the most recent entry, and the lowest value from column B to column W would be given by

A709: =MIN(INDEX(B$2:W$703,MATCH(1,0/ISBLANK(A$2:A$703)),0))

and the corresponding column label by

A710: =INDEX(B$1:W$1,MATCH(A709,INDEX(B$2:W$703,MATCH(1,0/ISBLANK(A$2:A$703)),0))

If A2:A703 is unsorted, it's more complicated.

A709: =MIN(INDEX(B$2:W$703,MATCH(MAX(A$2:A$703),A$2:A$703,0),0))

A710: =INDEX(B$1:W$1,MATCH(A709,INDEX(B$2:W$703,MATCH(MAX(A$2:A$703),A$2:A$703,0),0))

Thank you for the reply. I'm afraid when I try the bolded suggestions, I get DIV/0 errors. The third one appears to work fine! I will look further into how it's broken down.

Is there a way to remove "703" from the formula completely so that it adjusts to whatever row number the last row is?

[hrlngrv]

Sorry, screwed up.

Sorted
A709: =MIN(INDEX(B$2:W$703,MATCH(1,INDEX(0/NOT(ISBLANK(A$2:A$703)),0),0),0))
A710: =INDEX(B$1:W$1,MATCH(A709,INDEX(B$2:W$703,MATCH(1,INDEX(0/NOT(ISBLANK(A$2:A$703)),0),0),0),0))

Unsorted
A709: =MIN(INDEX(B$2:W$703,MATCH(MAX(A$2:A$703),A$2:A$703,0),0))
A710: =INDEX(B$1:W$1,MATCH(A709,INDEX(B$2:W$703,MATCH(MAX(A$2:A$703),A$2:A$703,0),0))

As for removing the $703, if these formulas would always be about 6 rows below the table, you could replace A$703 with INDEX($A:$A,ROW()-6) and W$703 with INDEX($W:$W,ROW()-6) in these formulas.

[audiberry]

Sorry, trying the Sorted formulas gives "Value not Available" #N/A errors.

The first Unsorted formula works fine, but the second Unsorted formula also gives a "Value not Available" error.

I'm wondering, could a minif be used?

minif the value in column B:W if A=max(A:A)?

[hrlngrv]

I should learn to test everything.

Sorted
A709: =MIN(INDEX(B$2:W$703,MATCH(1,INDEX(0/NOT(ISBLANK(A$2:A$703)),0)),0))
A710: =INDEX(B$1:W$1,MATCH(A709,INDEX(B$2:W$703,MATCH(1,INDEX(0/NOT(ISBLANK(A$2:A$703)),0)),0),0))

Unsorted
A709: =MIN(INDEX(B$2:W$703,MATCH(MAX(A$2:A$703),A$2:A$703,0),0))
A710: =INDEX(B$1:W$1,MATCH(A709,INDEX(B$2:W$703,MATCH(MAX(A$2:A$703),A$2:A$703,0),0),0))

I've tested these in this Excel Online workbook.

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!