# Look for year within a span

#### JensZ

##### New Member
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:

 -1948 Data B1 Data C1 1949-1954 Data B2 Data C2 1955-1959 Data B3 Data C3 1960- Data B4 Data C4

<tbody>
</tbody>

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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
If data is is in A1:C4, This Array formula gives 2, because 1949 is available in A2.For 1957 it gives blank.
Code:
``=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.

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.

Last edited:
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:

 -1948 Data 1 Data 2 1949-1954 Data 3 Data 4 1955-1959 Data 5 Data 6 1960- Data 7 Data 8

<tbody>
</tbody>

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!

ADVERTISEMENT
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.

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?

Use the VALUE function to convert to number.

If data is is in A2:C5, This Array formula gives value in B column.
Code:
``=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.

Replies
2
Views
203
Replies
7
Views
419
Replies
3
Views
601
Replies
23
Views
5K
Replies
9
Views
2K

Threads
1,196,467
Messages
6,015,401
Members
441,890
Latest member
Liero131313

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back