Find COLUMN number based upon cell contents

[fedude]

I have a large list of scores from a league I run - many years worth in my "Scores" tab. For a large number of formulas I need to locate the start of the data for the current year (i.e. the column number). I have been doing this manually every year, and I usually forget... There is a year in each column of this data (2007, 2008, etc.) in row 4.

What I need is a formula that will tell me the column number of the first column of a year based upon the contents of another cell in my "Tables" tab. So for instance if I enter 13 for the current year [Year], the YearStartCol will update to 122 (4x26+18)

Spreadsheet attached

[TMS]

Be better if you put the full year in. Then you could use Match:

Please Login or Register  to view this content.

You could always use:

Please Login or Register  to view this content.

Regards, TMS

[fedude]

Thanks TMS. I'm getting close. Match is definitely the function I need. However, the data types are proving problematic. The (2-digit) Year in the Tables tab is a number while the (4-digit) years in the Scores tab are text fields.

=MATCH(2000+B1,Scores!4:4,0)

returns #N/A

However,

=MATCH("20"&TEXT(B1,0),Scores!4:4,0)

works, but only for years starting at 2010.

So, I could put an IF statement in the function, like:

=MATCH(IF(B1<10,"200","20")&TEXT(B1,0),Scores!4:4,0)

Works!

Thanks for your help!

[TMS]

You're welcome. Thanks for the rep.

You could just use:

Please Login or Register  to view this content.

Regards, TMS