How do I return a header of column based on a range of cells containing specific data?

[Dena]

I have two spreadsheets. On one of them it has a column that I want to input a formula that will look at the 2nd sheet and in a specific range of cells looks for "12-13'". If one of those cells contains that data, I want the return value to be the header of the column that the data was found in.

This is the formula I have tried and it doesn't work, any suggestions. I want the formula in Sheet 1 F3 and I will drag it down once it works.

=IF('Sheet2'!$D3:$J3<>"12-13'",INDEX('Sheet2'!$D$2:$J$2,3,MATCH("12-13'",'Sheet 2'!$D3:$J3,0)),"")

Test Spreadsheet.xlsx

[mrice]

Try this user definded function

Function GetCycleYear(StudentName, YearOfInterest, NameColumn, ReturnValueRow) As String
SourceRow = NameColumn.Find(StudentName, , xlValues, xlWhole).Row
YearColumn = NameColumn.Parent.Rows(SourceRow).Find(YearOfInterest, , xlValues, xlWhole).Column
GetCycleYear = NameColumn.Parent.Cells(ReturnValueRow.Row, YearColumn).Value
End Function

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In Sheet2 F3, enter =GetCycleYear(B3,D3,Sheet1!A:D,Sheet1!$2:$2)

Remember to save the workbook as a macro enabled workbook .xlsm

[Dena]

That didn't seem to work, it converted everything to a R3-Year 3-Reg which in accurate. Does dragging the formula down the sheet not work with this type of thing. I have never used macros.

[mrice]

Should behave like a normal formula. Is your calculation set to automatic?

[Dena]

I have no idea but I think I know what the problem might be. You have it as =GetCycleYear(B3,D3,Sheet1!A:D,Sheet1!$2:$2) and D3 is not where I want it looking for 12-13'. I actually could remove that column. It needs to pull the info from sheet 1. Maybe I am reading this incorrectly.

[mrice]

Please see the attachment.

[Dena]

I guess I did something wrong. Thank you so much, it's working.