Matching formula that is too hard for me!

[bran987]

Hi everyone,

Here are the things that are in each column.

Column A - Phone Numbers
Column B - Dates
Column C - Dates
Column D - Record ID's
Column E - BLANK
Column F - Dates
Column G - Phone Numbers

I'm trying to write a matching or lookup formula that looks up and down these columns and does this:

If A MATCHES G
and F is ON or BETWEEN B and C
then return the value in column D into column E

I really appreciate you guys you've helped me so much over the years.

 

[DanteAmor]

Try this

=IF(AND(A2=G2,B2<=F2,C2>=F2),D2,"")

 

[bran987]

Hi DanteAmor,

Thank you, I guess I wasn't totally clear. The phone numbers in columns A and G could be on totally different rows, so I'm trying to make it so it looks all the way up and down both of columns A and G to find matches before it does the rest of the formula.

If I knew how to post a screenshot for you I promise I would! Does that make sense?

 

[bran987]

Is there a thread that teaches me how to post an example so you can see it? I don't think I was being clear enough.

 

[Fluff]

Is this what you want

Excel 2013/2016
	A	B	C	D	E	F	G
2	AL1 1HB	01/01/2019	01/02/2019	d2		11/01/2019	AL5 5AX
3	AL1 1WB	02/01/2019	02/02/2019	d3		12/01/2019	AL5 4NG
4	AL1 2NF	03/01/2019	03/02/2019	d4		13/01/2019	AL5 3QA
5	AL1 4HF	04/01/2019	04/02/2019	d5		14/01/2019	AL5 2UR
6	AL1 5JW	05/01/2019	05/02/2019	d6	d26	15/01/2019	AL5 2GX
7	AL10 0PY	06/01/2019	06/02/2019	d7	d25	16/01/2019	AL5 1QE
8	AL10 1DJ	07/01/2019	07/02/2019	d8	d24	17/01/2019	AL4 9YZ
9	AL10 8LG	08/01/2019	08/02/2019	d9	d23	18/01/2019	AL4 9NH
10	AL10 9NE	09/01/2019	09/02/2019	d10	d22	19/01/2019	AL4 8WD
11	AL2 1AU	10/01/2019	10/02/2019	d11	d21	20/01/2019	AL4 8EG
12	AL2 2ES	11/01/2019	11/02/2019	d12	d20	21/01/2019	AL4 0SP
13	AL2 3PT	12/01/2019	12/02/2019	d13	d19	22/01/2019	AL4 0EL
14	AL3 4AT	13/01/2019	13/02/2019	d14	d18	23/01/2019	AL3 8JR
15	AL3 5LE	14/01/2019	14/02/2019	d15	d17	24/01/2019	AL3 7NS
16	AL3 6RJ	15/01/2019	15/02/2019	d16	d16	25/01/2019	AL3 6RJ
17	AL3 7NS	16/01/2019	16/02/2019	d17	d15	26/01/2019	AL3 5LE
18	AL3 8JR	17/01/2019	17/02/2019	d18	d14	27/01/2019	AL3 4AT
19	AL4 0EL	18/01/2019	18/02/2019	d19	d13	28/01/2019	AL2 3PT
20	AL4 0SP	19/01/2019	19/02/2019	d20	d12	29/01/2019	AL2 2ES
21	AL4 8EG	20/01/2019	20/02/2019	d21	d11	30/01/2019	AL2 1AU
22	AL4 8WD	21/01/2019	21/02/2019	d22		31/01/2019	AL10 9NE
23	AL4 9NH	22/01/2019	22/02/2019	d23		01/02/2019	AL10 8LG
24	AL4 9YZ	23/01/2019	23/02/2019	d24		02/02/2019	AL10 1DJ
25	AL5 1QE	24/01/2019	24/02/2019	d25		03/02/2019	AL10 0PY
26	AL5 2GX	25/01/2019	25/02/2019	d26		04/02/2019	AL1 5JW
TB1

Cell Formulas
Range	Formula
E2	{=IFERROR(INDEX($D$2:$D$30,MATCH(1,($G$2:$G$30=A2)*($F$2:$F$30>=B2)*($F$2:$F$30<=C2),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bran987]

Book1
	A	B	C	D	E	F	G
1	Phone Number	Date	Date	Record ID	Result	Date	Phone Number
2	214-986-7777	12/30/2019	1/5/2020	100		1/3/2020	214-986-7777
3	214-976-5555	12/30/2019	1/5/2020	200	300	1/7/2020	214-765-0989
4	214-765-0989	1/6/2020	1/13/2020	300		5/2/2019	214-986-7777
5	214-473-0909	1/6/2020	1/13/2020	400	500	1/9/2020	214-473-0909
6	214-473-0909	1/6/2020	1/13/2020	500		1/2/2020	214-986-7777
Sheet1

I figured out how to post in HTML! See how the phone number in G5 matches the phone number in A6, and because the date in F5 is between the dates in B6 and C6, it returns the value in D6 up into E5?

 

[DanteAmor]

	A	B	C	D	E	F	G
Phone Number	Date	Date	Record ID	Result	Date	Phone Number
214-986-7777	214-986-7777
214-976-5555	214-765-0989
214-765-0989	214-986-7777
214-473-0909	214-473-0909
214-473-0909	214-986-7777

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12/30/2019[/TD]
[TD="align: right"]1/5/2020[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/3/2020[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]12/30/2019[/TD]
[TD="align: right"]1/5/2020[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1/7/2020[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1/6/2020[/TD]
[TD="align: right"]1/13/2020[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5/2/2019[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/6/2020[/TD]
[TD="align: right"]1/13/2020[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1/9/2020[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1/6/2020[/TD]
[TD="align: right"]1/13/2020[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/2/2020[/TD]

</tbody>

Sheet1​

I figured out how to post in HTML! See how the phone number in G5 matches the phone number in A6, and because the date in F5 is between the dates in B6 and C6, it returns the value in D6 up into E5?

try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:98.85px;" /><col style="width:106.46px;" /><col style="width:103.6px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:143.52px;" /><col style="width:134.02px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">PHONE</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATES</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATES</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ID</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">RESULT</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATES</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">PHONE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >214-986-7777</td><td style="text-align:right; ">30/dic/2019</td><td style="text-align:right; ">05/ene/2020</td><td style="text-align:right; ">100</td><td > </td><td style="text-align:right; ">03/ene/2020</td><td >214-986-7777</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >214-976-5555</td><td style="text-align:right; ">30/dic/2019</td><td style="text-align:right; ">05/ene/2020</td><td style="text-align:right; ">200</td><td style="text-align:right; ">300</td><td style="text-align:right; ">07/ene/2020</td><td >214-765-0989</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >214-765-0989</td><td style="text-align:right; ">06/ene/2020</td><td style="text-align:right; ">13/ene/2020</td><td style="text-align:right; ">300</td><td > </td><td style="text-align:right; ">02/may/2019</td><td >214-986-7777</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >214-473-0909</td><td style="text-align:right; ">06/ene/2020</td><td style="text-align:right; ">13/ene/2020</td><td style="text-align:right; ">400</td><td style="text-align:right; ">500</td><td style="text-align:right; ">09/ene/2020</td><td >214-473-0909</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >214-473-0909</td><td style="text-align:right; ">06/ene/2020</td><td style="text-align:right; ">13/ene/2020</td><td style="text-align:right; ">500</td><td > </td><td style="text-align:right; ">02/ene/2020</td><td >214-986-7777</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=IFERROR(INDEX($D$3:$D$7,SUMPRODUCT((A3:$A$7=G2)*(B3:$B$7<=F2)*(C3:$C$7>=F2)*(ROW(D3:$D$7)))-2),"")</td></tr></table></td></tr></table>

 

[bran987]

try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:98.85px;" /><col style="width:106.46px;" /><col style="width:103.6px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:143.52px;" /><col style="width:134.02px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">PHONE</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATES</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATES</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ID</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">RESULT</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">DATES</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">PHONE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >214-986-7777</td><td style="text-align:right; ">30/dic/2019</td><td style="text-align:right; ">05/ene/2020</td><td style="text-align:right; ">100</td><td > </td><td style="text-align:right; ">03/ene/2020</td><td >214-986-7777</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >214-976-5555</td><td style="text-align:right; ">30/dic/2019</td><td style="text-align:right; ">05/ene/2020</td><td style="text-align:right; ">200</td><td style="text-align:right; ">300</td><td style="text-align:right; ">07/ene/2020</td><td >214-765-0989</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >214-765-0989</td><td style="text-align:right; ">06/ene/2020</td><td style="text-align:right; ">13/ene/2020</td><td style="text-align:right; ">300</td><td > </td><td style="text-align:right; ">02/may/2019</td><td >214-986-7777</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >214-473-0909</td><td style="text-align:right; ">06/ene/2020</td><td style="text-align:right; ">13/ene/2020</td><td style="text-align:right; ">400</td><td style="text-align:right; ">500</td><td style="text-align:right; ">09/ene/2020</td><td >214-473-0909</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >214-473-0909</td><td style="text-align:right; ">06/ene/2020</td><td style="text-align:right; ">13/ene/2020</td><td style="text-align:right; ">500</td><td > </td><td style="text-align:right; ">02/ene/2020</td><td >214-986-7777</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=IFERROR(INDEX($D$3:$D$7,SUMPRODUCT((A3:$A$7=G2)*(B3:$B$7<=F2)*(C3:$C$7>=F2)*(ROW(D3:$D$7)))-2),"")</td></tr></table></td></tr></table>

Thanks Dante! I had to add some $'s, change all the 3's in the formulas to 2's, and it ended up being -1 that worked on the ROW part instead of -2 (don't know why) but now it works!!!!!!!!
Many thanks!

 

[DanteAmor]

Thanks Dante! I had to add some $'s, change all the 3's in the formulas to 2's, and it ended up being -1 that worked on the ROW part instead of -2 (don't know why) but now it works!!!!!!!!
Many thanks!

The formula starts at E2, but it should only be checked at E3 down.
The E6 formula will only revise the E7. According to the logic after E6 there is nothing that can go up.

The important thing is that now it works for you.
It was a pleasure to help you.