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

[zpenacho]

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!

[TMS]

Does this give you what you want?

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


Regards

[watersev]

hi, sort of that

[MarvinP]

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.

[zpenacho]

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!

[TMS]

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