Results 1 to 3 of 3

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    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


  3. #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
  •