+ Reply to Thread
Results 1 to 6 of 6

Need to search array and return value along with title and row values

  1. #1
    Registered User
    Join Date
    09-21-2013
    Location
    TX
    MS-Off Ver
    2010
    Posts
    10

    Need to search array and return value along with title and row values

    I have used the SMALL and LARGE functions to create a bottom and top 10, but I need to include the corresponding row and column names to identify those values. I have attached a sample.
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Need to search array and return value along with title and row values

    In J3 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

    =IFERROR(INDEX($A$1:$H$1,,MIN(IF($B$2:H13=J2,COLUMN($B$2:$H$13)))),"")

    In J7 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

    =IFERROR(INDEX($A$1:$H$1,,MIN(IF($B$2:H13=J6,COLUMN($B$2:$H$13)))),"")

    Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

    Drag both the formula's to right...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Need to search array and return value along with title and row values

    Hi wilburr,

    one more approach...
    In J3 use formula as..

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    format the cell as "DD-MM-YYYY HH:MM"

    and copy the same in J7 too.. or drag rightward.

    PS: change A2 to 0 for better view..
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Need to search array and return value along with title and row values

    @ Debraj Roy,

    The reason for avoiding sumproduct approach is it will Fail/Throw an Error if there is multiple matches. Because sumproduct accumulates all the matches

  5. #5
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Need to search array and return value along with title and row values

    @ :) Sixthsense :)

    I agree boss... :)
    but for 2D search.. I know only this method.. need some time to learn more.. ;(

    Keep guiding..

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,684

    Re: Need to search array and return value along with title and row values

    In fact me too thought to go for the same approach which you shown in Post #3, but because of possibility of duplicate records I need to go for Array solution

    I just wanted to let you know the duplicate risk if you are not aware.

    Thanks for taking it in a positive way

  7. #7
    Registered User
    Join Date
    09-21-2013
    Location
    TX
    MS-Off Ver
    2010
    Posts
    10

    Re: Need to search array and return value along with title and row values

    I used the first formula provided and it resulted in a number. I used the comments from the second responder to format the cell and it almost worked.

    Two questions.
    1. how do I format the cell to show the results desired in the attached/
    2. Would you walk me through the first formula so I understand it better and do not need to ask the question again when working on a larger spreadsheet containing multiple weeks.

    TIA

    Wilburr
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 12-21-2012, 10:35 AM
  2. Find a value in an array and return multiple values in an adjacent array
    By tonbra in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2012, 08:35 PM
  3. [SOLVED] Search array and return rows?
    By marrott2 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-25-2011, 02:11 AM
  4. Search array for text, return column #
    By Spreadsheet in forum Excel General
    Replies: 5
    Last Post: 05-09-2007, 12:30 PM
  5. Search array and return element No
    By Ron in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-17-2006, 12:35 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1