How to obtain a value in a column cell contingent on the text entered in the column header

[teene12]

Hello, I have been beating my brain with this.

I am trying to populate the value of a cell in a column when a specific text is entered in that column header cell.

For example:

If I enter the text of any given month in cell header "CQ5", let's say "Sep", I want the same values for that month "CW6-CW11" to populate in cells "CQ6-CQ11". I want the same info for the indicated month to be mirrored in column "CQ". If I enter the text for a different month in "CQ5", then I want the same result. That month's values populated in column "CQ".

I have tried the following formula as a guess, but, I think since the data is in the middle of a spreadsheet the result is coming up wrong. I have other data within my spreadsheet that are not included in the result I am looking for.

=HLOOKUP($CQ$5,$CR$5:$DC$11,ROW(),0)

I have an example attached. Any assistance will be greatly appreciated.

[CRIMEDOG]

=INDEX($CR$5:$DC6,ROW()-4,MATCH($CQ$5,$CR$5:$DC$5,0))
And Copy it down

If you make CQ5 a conditional list - it forces a correct option (Instead of mistype)

[protonLeah]

In your formula the table range is: $CR$5:$DC$11. Note that the first row of the range is actually row five of the worksheet. Therefore your data values (row two of the range, start on row six of the sheet. Using ROW() with empty parentheses returns the worksheet row of the cell the formula is in (row six in this case) to the formula. So your formula starts counting at ws row 5 and returns the value 6 rows down from there. You actually want to start at range row 2, so:

Please Login or Register  to view this content.

**ROW(A2) returns 2 to the formula.

[teene12]

I implemented the formula CRIMEDOG provided and it worked by providing the result I needed.

Thank you CRIMEDOG and protonLeah for your responses and assistance.