# Thread: Complicated Match/Lookup in between dates formula? Thanks: 0 Likes:  2 Post #5326585 (1)Post #5327155 (1)

1. ## Complicated Match/Lookup in between dates formula?

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

Name ID Date Start Date End Phone Number
 Apple
375
 12/30/2018
 1/5/2019
 210-908-5231
Pear 224
 12/30/2018
 1/5/2019
 210-361-8442
Grape 846
 3/26/2019
 4/1/2019
 210-361-5592
Lemon 552
 5/26/2019
 6/1/2019
 210-361-5592

Table B

ID Master Date Phone Number
224 1/3/19
 210-361-8442
846 3/27/19 210-361-5592
846 4/22/19 210-361-5592

2. ## Re: Complicated Match/Lookup in between dates formula?

This array formula turned out.

Maybe someone else can help you with a shorter one.

 A B C D E F G H I 1 Name ID Date Start Date End Phone Number ID Master Date Phone Number 2 Apple 375 30/12/2018 05/01/2019 210-908-5231 224 03/01/2019 210-361-8442 3 Pear 224 30/12/2018 05/01/2019 210-361-8442 846 27/03/2019 210-361-5592 4 grape 846 26/03/2019 01/04/2019 210-361-5592 846 22/04/2019 210-361-5592 5 lemon 552 26/05/2019 01/06/2019 210-361-5592

 Cell Array Formula G2 {=IFERROR(INDEX(\$B\$1:\$B\$5,LARGE(IF((\$E\$2:\$E\$5=I2)*(\$C\$2:\$C\$5<=H2)*(\$D\$2:\$D\$5>=H2),ROW(\$A\$2:\$A\$5)),1)),INDEX(\$B\$1:\$B\$5,MA X(IF(ABS(\$C\$2:\$C\$5-H2)=MIN(ABS(\$C\$2:\$C\$5-H2),ABS(\$D\$2:\$D\$5-H2)),ROW(\$B\$2:\$B\$5),IF(ABS(\$D\$2:\$D\$5-H2)=MIN(ABS(\$C\$2:\$C\$5-H2),ABS(\$D\$2:\$D\$5-H2)),ROW(\$B\$2:\$B\$5))))))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

3. ## Re: Complicated Match/Lookup in between dates formula?

Code:
`=sum(if(\$e\$2:\$e\$5=l2;if(k2>\$c\$2:\$c\$5;if(k2<\$d\$2:\$d\$5;j2;index(\$b\$2:\$b\$5;match(min(abs(\$c\$2:\$c\$5-k2));abs(\$c\$2:\$c\$5-k2);0)));index(\$b\$2:\$b\$5;match(max(abs(\$c\$2:c\$5-k2));abs(\$c\$2:\$c\$5-k2);0)));"no"))`
So, I used this formula and dragged the fill handle and get the exact results as you, 224;846;846. I already blocked the cells.
IMPORTANT: When you enter the formula, don't press just ENTER, instead press CTRL + SHIFT + ENTER. It will appear "{}" on the formula. This is because you have to cell by cell and compare with your master date.

Hope I could help.

4. ## Re: Complicated Match/Lookup in between dates formula?

Sorry, didn't see your response. Perhaps yours is better than mine. But they are very similar!

5. ## Re: Complicated Match/Lookup in between dates formula?

Originally Posted by tico_ocit
Sorry, didn't see your response. Perhaps yours is better than mine. But they are very similar!
Welcome to the forum!

Do not worry. The important thing is to deliver the best response to the OP.
They are similar, but you missed to include the dates in column D, to also verify with those dates which is the closest.

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

The same formula but a little shorter.

 Zelle Formel G2 {=IFERROR(INDEX(\$B\$1:\$B\$5,LARGE(IF((\$E\$2:\$E\$5=I2)*(\$C\$2:\$C\$5<=H2)*(\$D\$2:\$D\$5>=H2),ROW(\$A\$2:\$A\$5)),1)),INDEX(\$B\$1:\$B\$5,MAX(((ABS(\$D\$2:\$D\$5-H2)=MIN(ABS(\$C\$2:\$C\$5-H2),ABS(\$D\$2:\$D\$5-H2)))+(ABS(\$C\$2:\$C\$5-H2)=MIN(ABS(\$C\$2:\$C\$5-H2),ABS(\$D\$2:\$D\$5-H2))))*ROW(\$B\$2:\$B\$5))))}

6. ## Re: Complicated Match/Lookup in between dates formula?

Great' here i am staring at both the formulas

7. ## Re: Complicated Match/Lookup in between dates formula?

Originally Posted by DanteAmor
Welcome to the forum!

Do not worry. The important thing is to deliver the best response to the OP.
They are similar, but you missed to include the dates in column D, to also verify with those dates which is the closest.

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

The same formula but a little shorter.

 Zelle Formel G2 {=IFERROR(INDEX(\$B\$1:\$B\$5,LARGE(IF((\$E\$2:\$E\$5=I2)*(\$C\$2:\$C\$5<=H2)*(\$D\$2:\$D\$5>=H2),ROW(\$A\$2:\$A\$5)),1)),INDEX(\$B\$1:\$B\$5,MAX(((ABS(\$D\$2:\$D\$5-H2)=MIN(ABS(\$C\$2:\$C\$5-H2),ABS(\$D\$2:\$D\$5-H2)))+(ABS(\$C\$2:\$C\$5-H2)=MIN(ABS(\$C\$2:\$C\$5-H2),ABS(\$D\$2:\$D\$5-H2))))*ROW(\$B\$2:\$B\$5))))}

Code:
`=sum(if(\$e\$2:\$e\$5=l2;if(k2>\$c\$2:\$c\$5;if(k2<\$d\$2:\$d\$5;j2;index(\$b\$2:\$b\$5;match(min(abs(\$c\$2:\$c\$5-k2));abs(\$c\$2:\$c\$5-k2);0)));index(\$b\$2:\$b\$5;match(min(abs(\$c\$2:c\$5-k2));abs(\$c\$2:\$c\$5-k2);0)));"no"))`
I think I did compare, but used MAX when I should used MIN, because you always want the minimum difference.
I tryed yours and get all done. Although when you use
Code:
`LARGE(IF((\$E\$2:\$E\$5=I2)*(\$C\$2:\$C\$5<=H2)*(\$D\$2:\$D\$5>=H2)`
I don't know what "*" are for? Never used before.
Been wondering why your formula use "," and mine ";".

8. ## Re: Complicated Match/Lookup in between dates formula?

Originally Posted by tico_ocit
Code:
`=sum(if(\$e\$2:\$e\$5=l2;if(k2>\$c\$2:\$c\$5;if(k2<\$d\$2:\$d\$5;j2;index(\$b\$2:\$b\$5;match(min(abs(\$c\$2:\$c\$5-k2));abs(\$c\$2:\$c\$5-k2);0)));index(\$b\$2:\$b\$5;match(min(abs(\$c\$2:c\$5-k2));abs(\$c\$2:\$c\$5-k2);0)));"no"))`
I think I did compare, but used MAX when I should used MIN, because you always want the minimum difference.
I tryed yours and get all done. Although when you use
Code:
`LARGE(IF((\$E\$2:\$E\$5=I2)*(\$C\$2:\$C\$5<=H2)*(\$D\$2:\$D\$5>=H2)`
I don't know what "*" are for? Never used before.
Been wondering why your formula use "," and mine ";".
"*" is a nested if.

if (condition, if (condition, true))
or
if (condition * condition, true)

"," or ";" It is the argument separator that you have configured by region.

9. ## Re: Complicated Match/Lookup in between dates formula?

These do not seem to be working when I switch the columns I am using into the formula and I can not figure out why. I could send you the actual workbook I am using, however I do not see an attachment button anywhere.

The formula seems to not have errors, however it is pulling the incorrect numbers.

10. ## Re: Complicated Match/Lookup in between dates formula?

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.

Sheet1

 Related Patient Related Weekly Spend Date Time Tracking Number #N/A 6/2/2019 3:56 PM 210-960-9944 #N/A 6/1/2019 2:52 PM 210-239-2304 #N/A 6/1/2019 2:12 PM 210-610-9726 #N/A 6/1/2019 11:35 AM 210-263-3116 #N/A 5/31/2019 2:30 PM 210-361-3306 29787 5/31/2019 2:25 PM 210-610-9726 #N/A 5/31/2019 1:16 PM 210-239-2304 29632 5/31/2019 10:35 AM 210-361-1720 #N/A 5/30/2019 3:05 PM 210-610-9726 29634 5/30/2019 2:57 PM 210-610-9726 19666 5/30/2019 2:28 PM 210-960-9944 #N/A 5/30/2019 1:44 PM 210-361-1720 #N/A 5/30/2019 11:56 AM 210-944-4129 29626 5/30/2019 10:21 AM 210-361-1720 #N/A 5/30/2019 10:20 AM 210-361-1720 #N/A 5/30/2019 10:20 AM 210-361-1720 29622 5/30/2019 10:18 AM 210-361-1720 #N/A 5/30/2019 10:17 AM 210-361-1720 #N/A 5/30/2019 10:17 AM 210-361-1720 #N/A 5/30/2019 10:16 AM 210-361-1720 #N/A 5/30/2019 10:16 AM 210-361-1720 29624 5/30/2019 10:16 AM 210-361-1720 #N/A 5/30/2019 10:16 AM 210-361-1720 29625 5/30/2019 10:16 AM 210-361-1720

Sheet 2

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•