+ Reply to Thread
Results 1 to 6 of 6

Returning a Specific Cell's Column Header from a Row in an Array

  1. #1
    Registered User
    Join Date
    06-25-2011
    Location
    Chicago, IL
    MS-Off Ver
    MS 365 apps for business
    Posts
    63

    Returning a Specific Cell's Column Header from a Row in an Array

    I was messing around with a workbook for my fantasy football league and created a brain teaser.

    On the QB sheet I want to return the week number when that particular QB has a bye from the Schedule sheet. For example, for Michael Vick and PHI, it should return 7. For Rodger and GB, it should return 8.

    I think it's some nested lookup formula?

    I know it's probably faster to do this manually with so few records, but I want to know what's up in-case I encounter this problem in a work related book.

    I've attached the small workbook to make answering the question easier. Thanks!
    Attached Files Attached Files
    Last edited by zpenacho; 06-25-2011 at 07:17 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,967

    Re: Returning a Specific Cell's Column Header from a Row in an Array

    Does this give you what you want?

    =IFERROR(INDEX($D$1:$H$1,MATCH("BYE",$D2:$H2,0)),"No Bye")


    Regards
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Returning a Specific Cell's Column Header from a Row in an Array

    hi, sort of that
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,805

    Re: Returning a Specific Cell's Column Header from a Row in an Array

    Hi zpenacho and welcome to the forum,

    Put the following formula in Cell C2 and pull it down.
    Please Login or Register  to view this content.
    This will show which week this Team in Col B has a BYE.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    06-25-2011
    Location
    Chicago, IL
    MS-Off Ver
    MS 365 apps for business
    Posts
    63

    Re: Returning a Specific Cell's Column Header from a Row in an Array

    MarvinP and watersev your formulas both worked great, thanks! Not too familiar with MATCH or INDIRECT, but will look into them now. Your formulas were a little different at the tails, but it didn't seem to matter.

    TMShucks your formula was close, but gave some QBs "No Bye", which isn't possible. Not sure what was up.

    Thanks everyone, I look forward to spending more time on this forum!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,967

    Re: Returning a Specific Cell's Column Header from a Row in an Array

    Thanks for the feedback. I misunderstood the requirement and thought you needed the header from the same sheet. My mistake. Credit to MarvinP and watersev for a) understanding the question and b) coming up with their solutions.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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