Find offset value when I only have the colum number

[brianfosterblack]

Can someone please help me with this
I am working in a worksheet (named Report) and have to find the person ranked first in a certain category on another worksheet
The other worksheet is named "scores"
I have used a match formula to find the applicable category on the scores sheet between a range AL2:BL2
From there I got the column number (In this case column 39) and then using the address formula I got the column address "Scores!AM2" - This result in the formula reflects in Report!I19
That is where I am stuck
What I need help with is the rest of the formula to find the number 1 in the column matched (it will be between row 3 and 203 of that column) and then pick up the name in the same row in Range Scores!C3 to Score!C203

Any assistance would be most welcome
Thank you.

 

[AhoyNC]

Try:

Excel Workbook
	A	B
1	Match	Test4
2	Name =	Name5
Report

Excel Workbook
	C	AK	AL	AM	AN	AO	AP	AQ
2	Name		Test1	Test2	Test3	Test4	Test5	Test6
3	Name1		55	53	104	132	179	188
4	Name2		45	100	1	113	127	2
5	Name3		93	86	32	186	1	85
6	Name4		104	1	58	86	193	44
7	Name5		100	131	2	1	163	61
8	Name6		1	7	163	72	56	194
9	Name7		196	184	193	189	41	1
10	Name8		7	192	179	94	15	84
Score

 

[brianfosterblack]

Try:

Report

	A	B
1	Match	Test4
2	Name =	Name5

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =IFERROR(INDEX(Score!$C$3:$C$203,MATCH(1,INDEX(Score!$AL$3:$BL$203,0,MATCH(Report!$B$1,Score!$AL$2:$BL$2,0)),0)),"No Match") <tbody> </tbody>

<tbody>
</tbody>

Score

	C	AK	AL	AM	AN	AO	AP	AQ
2	Name		Test1	Test2	Test3	Test4	Test5	Test6
3	Name1		55	53	104	132	179	188
4	Name2		45	100	1	113	127	2
5	Name3		93	86	32	186	1	85
6	Name4		104	1	58	86	193	44
7	Name5		100	131	2	1	163	61
8	Name6		1	7	163	72	56	194
9	Name7		196	184	193	189	41	1
10	Name8		7	192	179	94	15	84

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you for this but I am getting an answer of "No Match"
I presume that in your Formula "MATCH(Report!$B$1,)" - Report!B1 is the value which must be found in the range Score!$AL$2:$BL$2? On my workbook, the value to be matched is in cell Report!$D$18. I made this change but still got "No Match"

 

[brianfosterblack]

Sorry, I have been playing around with this to try and find the problem.
I found my sheet is named Scores and not score and the range is AL2:BI2 - not BL2
I changed these and it still did not work.
Then I realised the problem. The reason I started by finding the column number is that in the Range AL2:BI2 I have a lot of column headings like M,S,V, which have scores in them. But next to each of these I have a column named "Rank" Each of the scores are ranked in the Rank column and the number 1 I am looking for will be found in one of the Rank columns. So if my match column is found, I want to find the number 1 in the column just to the right of that.
Sorry, I did not know you would use a different formula completely from mine. But thank you for your patience

 

[brianfosterblack]

After explaining my problem I have sorted out the formula.
=IFERROR(INDEX(Scores!$C$3:$C$202,MATCH(1,INDEX(Scores!$AL$3:$BI$202,0,MATCH(Report!$D$17,Scores!$AL$2:$BI$2,0)+1),0)),"No Match")
By putting in the +1 I resolved it
thank you so much for your assistance - it works perfectly
It is much appreciated

 

[AhoyNC]

If D18 holds the value of the header to return in Score!$AL$2:$BL$2 see what this returns.

=MATCH($D$18,Score!$AL$2:$BL$2,0) It should return a number for the column.

<colgroup><col width="315"></colgroup><tbody>
</tbody>

If you get an #NA it means it didn't find a match. If that's the case check the spelling and make sure there are no leading or ending spaces in either the header or cell D18.

If that's not the issue please post the actual formula you are using.

 

[AhoyNC]

Just saw that you got it to work. Thanks for the feedback.