+ Reply to Thread
Results 1 to 4 of 4

selecting cell in array by matching row value in a column and matching column number

  1. #1
    Registered User
    Join Date
    01-22-2019
    Location
    ny
    MS-Off Ver
    2016
    Posts
    22

    selecting cell in array by matching row value in a column and matching column number

    Hi,
    I would like to know how to do this.
    So in the attached sample file, rows for all columns B to F will only have value 1. So for all the rows, only one column from B to F will have value 1.
    Now, I want to get the category name and value in Cell J2 and K2, corresponding to date in cell I2 using a formula.
    How to do this

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: selecting cell in array by matching row value in a column and matching column number

    in J2
    =INDEX(B1:G1,MAX(IF((B2:G8=1)*(A$2:A$8=I2),COLUMN(B1:G1)-COLUMN(B1)+1)))
    Array formula, use Ctrl-Shift-Enter

    in K2
    =INDEX(G2:G8,MATCH(I2,A$2:A$8,0),1)
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    01-22-2019
    Location
    ny
    MS-Off Ver
    2016
    Posts
    22

    Re: selecting cell in array by matching row value in a column and matching column number

    Quote Originally Posted by Special-K View Post
    in J2
    =INDEX(B1:G1,MAX(IF((B2:G8=1)*(A$2:A$8=I2),COLUMN(B1:G1)-COLUMN(B1)+1)))
    Array formula, use Ctrl-Shift-Enter

    in K2
    =INDEX(G2:G8,MATCH(I2,A$2:A$8,0),1)
    Thanks for sending the formula for J2, really appreciate it.
    One small change, there could be a case where there is no value i.e no 1 for certain rows for columns B to F, in that case, I would like J2 to give a value error or "#N/A"

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,429

    Re: selecting cell in array by matching row value in a column and matching column number

    This formula (regular) will yield a #NUM error if there is not a one in the specified row: =INDEX(B1:G1,AGGREGATE(14,6,(COLUMN(B:F)-COLUMN(A:A))/(A2:A10=I2)/(B2:F10=1),1))
    If you would rather have it display N/A wrap the formula inside IFERROR i.e.: =IFERROR(INDEX(B1:G1,AGGREGATE(14,6,(COLUMN(B:F)-COLUMN(A:A))/(A2:A10=I2)/(B2:F10=1),1)),"N/A")
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. dynamic array for matching value in another column
    By aleenkhan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2017, 08:20 PM
  2. [SOLVED] Matching a number in a column and returning a value from another column
    By Andreas_Zopo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-28-2016, 05:08 PM
  3. Array based on matching Row & Column
    By Katch22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-07-2015, 07:36 AM
  4. [SOLVED] Column matching and selecting offset data, VBA
    By Shearwater in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-23-2014, 10:48 AM
  5. [SOLVED] Search Column and Selecting Rows with Matching Text
    By themattberman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-04-2014, 11:35 PM
  6. Replies: 1
    Last Post: 07-25-2011, 01:07 PM
  7. Selecting matching items in a column
    By Panajohn in forum Excel General
    Replies: 2
    Last Post: 08-15-2005, 05:05 PM

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