+ Reply to Thread
Results 1 to 3 of 3

Array Formula to return column name if row contains "yes"

  1. #1
    Registered User
    Join Date
    03-17-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    51

    Unhappy Array Formula to return column name if row contains "yes"

    Hi All,

    I have a school project where I have to create a dynamic list to populate the toys where in the columns it states "yes". I have been practicing for ages by searching the forum and tried my best to learn from other sources and I seem to confuse myself. Below formula is what I compiled but its not working.

    The yellow is my formula "{=SMALL(INDEX(ISNUMBER($B$4:$I$4)*COLUMN($B$4:$I$4),0),COUNTBLANK($B$4:$I$4)+COLUMN(B3))}"
    The red cells show what result I need

    I have attached sample to give an idea.

    Can someone please help me with this formula?



    Thank you so much in advance!
    Attached Files Attached Files
    Last edited by Tai1001; 01-11-2021 at 02:05 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Array Formula to return column name if row contains "yes"

    One way, in L4, copied across and down:

    =IFERROR(INDEX($3:$3,AGGREGATE(15,6,COLUMN($B$3:$I$3)/($B4:$I4="Yes"),COLUMNS($L4:L4))),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    03-17-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    51

    Re: Array Formula to return column name if row contains "yes"

    Hi Glenn!

    This is perfect! Thank you so much.

    I did come across the aggregate function but had no clue what it is or how to use it.

    I just examined the formula and OMG I did not know you can nest function 15 as small and "options" 6 as ignore errors. I will try to learn this!

    Thank you!

+ 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: 1
    Last Post: 04-08-2020, 11:49 AM
  2. countif to return "x" for unique & "xx" for duplicate values in a column
    By Shruder in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2018, 05:31 PM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. compare cells from column "A" and return newest date from column "B"
    By toni1703 in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 01-12-2016, 08:33 AM
  5. Replies: 3
    Last Post: 04-14-2013, 11:53 PM
  6. Replies: 2
    Last Post: 12-25-2012, 08:49 AM
  7. [SOLVED] If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ...
    By Maria J-son in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2006, 08:25 AM

Tags for this Thread

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