If date from table is within date range, then return value from other table

DipDip

Board Regular
Joined
Jan 23, 2015
Messages
76
Office Version
  1. 2016
Platform
  1. Windows
Hi All,
I'm racking my brain on how to do this, but can't seem to find the answer. I'm really hoping someone can help me. I have a worksheet with a table that looks like the below:

ABCDEF
DateLabel 1Label 2Label 3Label 4Label 5
01/02/24123
02/02/24105
02/02/24100
16/02/24251
10/02/24500

I then have another table on the same worksheet that looks like this:

PQRST
01/02/2405/02/2412/02/2426/02/24
04/02/2411/02/2418/02/2429/02/24
Label 1Q3R3
Label 2Q4R4
Label 3Q5R5
Label 4Q6R6
Label 5Q7R7

What I want to achieve is that in Cell Q3, it returns 123. It looks up all the data in the first table, and if the date is within the range the date range above it, and also from Label 1, then it returns the value. There will never be more than one entry per Label per date range. But there could be repeats of the date, but with entries for other Labels. The dates also may not be in order of oldest to newest.

It would then look like the following if it worked:

PQRST
01/02/2405/02/2412/02/2426/02/24
04/02/2411/02/2418/02/2429/02/24
Label 1123
Label 2105
Label 3100
Label 4500
Label 5251

Thanks in advance for any help or pointers people can give me.

Dipam
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
I've entered this into Q3:

{=INDEX(B$2:B$58,MATCH(1,IF($A2>=$V$3:$V$7,IF($A2<=$W$3:$W$7,1)),0))}

And it worked for that first box but won't work for the others.

To the side I've added the dates so they are re-arranged in a better way. Hence V & W.

01/02/2024​
05/02/2024​
12/02/2024​
19/02/2024​
26/02/2024​
04/02/2024​
11/02/2024​
18/02/2024​
25/02/2024​
29/02/2024​
Label 1
123​
01/02/2024​
04/02/2024​
Label 2
05/02/2024​
11/02/2024​
Label 3
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
12/02/2024​
18/02/2024​
Label 4
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
19/02/2024​
25/02/2024​
Label 5
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
26/02/2024​
29/02/2024​
 
Upvote 0
Anyone please? Or at least help point me in the right direction so I can research and do it myself.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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