Look for year within a span

[JensZ]

Hi! New to the forum, bear with me!

I'm having this situation where I want to do a MATCH() to fetch data for a specific year, but the target cells consists of year-spans. Say I want to look for the row of year 1957 within a table that look like this:

[TABLE="width: 200"]
<tbody>[TR]
[TD]-1948[/TD]
[TD]Data B1[/TD]
[TD]Data C1[/TD]
[/TR]
[TR]
[TD]1949-1954[/TD]
[TD]Data B2[/TD]
[TD]Data C2[/TD]
[/TR]
[TR]
[TD]1955-1959[/TD]
[TD]Data B3[/TD]
[TD]Data C3[/TD]
[/TR]
[TR]
[TD]1960-[/TD]
[TD]Data B4[/TD]
[TD]Data C4[/TD]
[/TR]
</tbody>[/TABLE]

I can't split the year-spans because of external reference structure. Any tips on solving this issue is greatly appreciated!

 

[kvsrinivasamurthy]

If data is is in A1:C4, This Array formula gives 2, because 1949 is available in A2.For 1957 it gives blank.

=IFERROR(SMALL(IF(ISNUMBER(FIND([COLOR=#ff0000]1949[/COLOR],$A$1:$A$4)),ROW($A$1:$A$4),""),1),"")

How to enter ARRAY formula.
Paste the formula
Press F2
Press Ctrl+Shift+Enter Keys together.

 

[ztodd]

If the year-spans are guaranteed to not have any gaps, and to be in order, then maybe you can still use the match function with 1 or -1 for the match_type parameter.

...and if you are allowed to change that first span to be 0000-1948.

 

[JensZ]

Thank you so much for taking the time to help me. I realize my table in the question was a little misleading. Here's a better one:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]-1948[/TD]
[TD]Data 1[/TD]
[TD]Data 2[/TD]
[/TR]
[TR]
[TD]1949-1954[/TD]
[TD]Data 3[/TD]
[TD]Data 4[/TD]
[/TR]
[TR]
[TD]1955-1959[/TD]
[TD]Data 5[/TD]
[TD]Data 6[/TD]
[/TR]
[TR]
[TD]1960-[/TD]
[TD]Data 7[/TD]
[TD]Data 8[/TD]
[/TR]
</tbody>[/TABLE]

I'd rather not change the reference cells what so ever since I'm looking to reference files of stats that aren't created by me.

What I want is a formula that checks which year-span e.g. 1957 is within and give me back the data of that row. In the above case I want it to give me Data 5 and/or Data 6.

Thanks in advance!

 

[Oeldere]

add an new column (column A)

define the table (1948, 1949, 1955, 1960)

After that you can use this data (using a Vlookup) function.

e.g. e15=($d$15,$a$2:$d$4,3,1)

in which d15 contains the year.

a2:d4 host the table where you wan to tind the data.

3 is column 3 => in your example data 5.

1 is a NOT exactly match.

Please reply.

 

[JensZ]

That does the trick, thanks!

Problem is I would like the new column to generate itself. I tried using this formula: =IF(COUNT(A3)>5;RIGHT(A3;SEARCH("-";A3)-1);"") but I guess that the data type in above table A-column is not a numerical data type, because even though it seems to work and A3 shows "1955" it cannot be searched using a VLOOKUP-function. I'm guessing because it's really a text.

Any thoughts on this?

 

[ztodd]

Use the VALUE function to convert to number.

 

[kvsrinivasamurthy]

If data is is in A2:C5, This Array formula gives value in B column.

=INDEX($B$2:$B$5,MATCH(1957,VALUE(LEFT($A$2:$A$5,FIND("-",$A$2:$A$5)-1)),1))

How to enter ARRAY formula.
Paste the formula
Press F2
Press Ctrl+Shift+Enter Keys together.