Complicated Match/Lookup in between dates formula?

jwillits

New Member
Joined
Jul 10, 2018
Messages
36
Office Version
  1. 2010
Platform
  1. Windows
Objective: I am trying to obtain a number (ID) from Table A based off of the Date and Number in Table B. What I am trying to say in my formula is that if Phone Number in Table B is equal to a Phone Number in Table A, and the Date in Table B in the same row as that Phone Number is between the Start and End Date in Table A, then I want the corresponding ID from Table A generated in the first column in Table B.

Furthermore, if the phone number matches but it does not fall between a one of the list of dates, I need it to pull the ID from the closest range of dates before it.

For instance, the last row in Table B has a Master Date of 4/22/19, however there is no date range in Table A for it, so it is pulling the ID from the date ranges of 3-26-19 to 4-1-19 because it is the closest date ranges before the master date.

Hopefully this makes sense and there is some wizard out there who can accomplish this. Multiple formulas building on each other is fine as well.





Table A

NameIDDate StartDate EndPhone Number
Apple

<tbody>
</tbody>
375
12/30/2018

<tbody>
</tbody>
1/5/2019

<tbody>
</tbody>
210-908-5231

<tbody>
</tbody>
Pear224
12/30/2018

<tbody>
</tbody>
1/5/2019

<tbody>
</tbody>
210-361-8442

<tbody>
</tbody>
Grape846
3/26/2019

<tbody>
</tbody>
4/1/2019

<tbody>
</tbody>
210-361-5592

<tbody>
</tbody>
Lemon552
5/26/2019

<tbody>
</tbody>
6/1/2019

<tbody>
</tbody>
210-361-5592

<tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>



Table B

IDMaster DatePhone Number
2241/3/19
210-361-8442

<tbody>
</tbody>
8463/27/19210-361-5592
8464/22/19210-361-5592

<tbody>
</tbody>
 
Here is the actual data from my workbook. Note that this info is contained on different pages. In this case, could you write the formula trying to get the Related Weekly Spend number from Sheet2 into the column on Sheet1?

The rules are the same as that I need the Tracking Number to Match from Sheet1 to Sheet2, and then I need the Date in Sheet1 to fall between the Date of the Matching Tracking Number. If there is none, I need it to go to the date range before it.

To continue with the formula, it is important to answer the following:

It is always the previous date.
In an example.
If the date is 25may, the ranges are
a) 1apr - 20apr
b) 26may - 31may
So what is the corresponding range a) or b)?

The nearest date is 26may, but do you want the previous date?

---------------------------------------------

To adapt the formula to rows and columns and sheets, you could upload your file.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
To continue with the formula, it is important to answer the following:

It is always the previous date.
In an example.
If the date is 25may, the ranges are
a) 1apr - 20apr
b) 26may - 31may
So what is the corresponding range a) or b)?

The nearest date is 26may, but do you want the previous date?

---------------------------------------------

To adapt the formula to rows and columns and sheets, you could upload your file.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.


A would be correct, I need the nearest previous date, only if it is not between a set of dates.
 
Upvote 0
To adapt the formula to rows and columns and sheets, you could upload your file.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
Upvote 0
Been using Excel and Access for 13 years, and i am still a beginner :)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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