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

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

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

=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

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

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

Sam

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•