Look for year within a span

JensZ

New Member
Joined
Sep 18, 2014
Messages
4
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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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:
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,431
Messages
6,172,079
Members
452,444
Latest member
ShaImran193

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

Which adblocker are you using?

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
Back
Top