Return Column Number of Last Instance of a Numeric Value Found in Table

Sam

New member
Joined
Apr 3, 2014
Messages
36
Reaction score
0
Points
0
Excel Version(s)
2007
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
 

Attachments

  • Book5.xlsx
    10.8 KB · Views: 10
Adjust your Data Named Range to:

=OFFSET(Rawdata!$G$3,0,0,COUNTA(Rawdata!$G:$G),COUNTA(Rawdata!$4:$4)-1)

then assuming you list the criteria in AG1:AI1, in AG2 enter formula:

=LOOKUP(2,1/(INDEX(Data,MAX(INDEX((Data=AG1)*(ROW(Data)-MIN(ROW(Data))+1),0)),0)=AG1),INDEX(Data,1,0))

copied across
 
Thank you for your time and effort which is very much appreciated. The formula works great!

Sam
 
Back
Top