Hi All,
I am using Excel 2007 for Windows.
I have a table that consists of many columns and expanding rows; for this reason, I have referenced the table as a dynamic named range called “Data”. “Data” can contain duplicate values in both columns and rows. I have numeric labels in row 3, spanning the width of my table; “Data” starts in column “G”, row 4. Column “A” contains sequential numeric values (references) that can be used as relative row numbers for my table.
Scenario:
I would like to find the last instance of a specific numeric value (criterion) within “Data”, and have the column number (relative to my table) returned to a cell - using dynamic name “Data” in a flexible formula based solution with an input cell for the criterion.
Sample Layout:
Book5.xlsx attached
Expected Results:
Looking for last criterion of 0 (zero) in table –
Found in column “V”, row 14; return column number (relative to my table) = 16
Looking for last criterion of 7 in table –
Found in column “V”, row 13; return column number (relative to my table) = 16
Looking for last criterion of 14 in table –
Found in column “P”, row 18; return column number (relative to my table) = 10
Hope you can help.
Thanks,
Sam
I am using Excel 2007 for Windows.
I have a table that consists of many columns and expanding rows; for this reason, I have referenced the table as a dynamic named range called “Data”. “Data” can contain duplicate values in both columns and rows. I have numeric labels in row 3, spanning the width of my table; “Data” starts in column “G”, row 4. Column “A” contains sequential numeric values (references) that can be used as relative row numbers for my table.
Scenario:
I would like to find the last instance of a specific numeric value (criterion) within “Data”, and have the column number (relative to my table) returned to a cell - using dynamic name “Data” in a flexible formula based solution with an input cell for the criterion.
Sample Layout:
Book5.xlsx attached
Expected Results:
Looking for last criterion of 0 (zero) in table –
Found in column “V”, row 14; return column number (relative to my table) = 16
Looking for last criterion of 7 in table –
Found in column “V”, row 13; return column number (relative to my table) = 16
Looking for last criterion of 14 in table –
Found in column “P”, row 18; return column number (relative to my table) = 10
Hope you can help.
Thanks,
Sam