Approximate match / vlookup

[tiredofit]

The problem I have involves looking for records within 2 tables.

Table1, (the source), contains 100 records. Table2, (the sink), contains 1000 records.

So to check if each record in Table1 is contained in Table2, looping would involve 100x1000=100,000 times (if I'm not mistaken).

If instead I were to use a match or vlookup, I assume it will be faster?

If so, the problem is that I have other criteria to look up and one such criteria is if the date of a record in Table1 is within a week of the date of a record in Table2.

Using vlookup and match will only find exact matches, so is there a better solution?

Thanks

 

[Joe4]

VLOOKUP will find approximate matches. The optional 4th argument let's you indicate if you want an exact or approximate match.
See here for more details: https://www.extendoffice.com/documents/excel/2443-excel-vlookup-exact-approximate-match.html

 

[tiredofit]

Thanks but how can I adapt it for my particular requirement?

Besides, I am NOT allowed to sort the data beforehand.

 

[Joe4]

You haven't shown us any sample data, and described all the rules.
For example, what is there is a record with no exact match, but there is a record in the table that is one day earlier, and another that is one day later. Which one wins?

Besides, I am NOT allowed to sort the data beforehand.

If you are not able to sort the data, the only option I can come up with would be rather ugly, using VBA to go through each record, and check against every record in the other table.
Probably not the most efficient way, but I do not know of a better way. Maybe someone has some better ideas.

 

[tiredofit]

[TABLE="width: 682"]
<tbody>[TR]
[TD="colspan: 3, align: center"]Table1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3, align: center"]Table2[/TD]
[/TR]
[TR]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD="align: right"]05/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD="align: right"]04/01/2010[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]b[/TD]
[TD="align: right"]06/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]c[/TD]
[TD="align: right"]03/01/2010[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]c[/TD]
[TD="align: right"]07/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]d[/TD]
[TD="align: right"]04/01/2010[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]d[/TD]
[TD="align: right"]08/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]e[/TD]
[TD="align: right"]03/01/2010[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]e[/TD]
[TD="align: right"]09/01/2010[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]b[/TD]
[TD="align: right"]06/01/2010[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4, align: center"]Output[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Date[/TD]
[TD]Pass[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]a[/TD]
[TD="align: right"]05/01/2010[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]b[/TD]
[TD="align: right"]06/01/2010[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]c[/TD]
[TD="align: right"]07/01/2010[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]d[/TD]
[TD="align: right"]08/01/2010[/TD]
[TD]y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]e[/TD]
[TD="align: right"]09/01/2010[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The reason why the Output in the second row is N is that for the combination 2b, its date in Table1 (06/1/2010) is NOT after the corresponding date in Table2.

This is what I mean by not an exact match when doing a lookup.

 

[Joe4]

Let's see if I am understanding this correctly.
So, you are matching on two fields (Field1 and Field2), and comparing the Dates?
And the Date in Table1 needs to be AFTER the Table2?

When matching between the two tables on Fields 1 and 2, is it always a one-to-one match?
Or might there be no match, or multiple matches?

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I
1	Table1						Table2
2	Field1	Field2	Date	Pass			Field1	Field2	Date
3	1	a	05/01/2010	Y			1	a	04/01/2010
4	2	b	06/01/2010	N			3	c	03/01/2010
5	3	c	07/01/2010	Y			4	d	04/01/2010
6	4	d	08/01/2010	Y			5	e	03/01/2010
7	5	e	09/01/2010	Y			2	b	06/01/2010
Sheet1

Cell Formulas
Range	Formula
D3	=IF(C3>INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0)),"Y","N")

 

[tiredofit]

Let's see if I am understanding this correctly.
So, you are matching on two fields (Field1 and Field2), and comparing the Dates?
And the Date in Table1 needs to be AFTER the Table2?

When matching between the two tables on Fields 1 and 2, is it always a one-to-one match?
Or might there be no match, or multiple matches?

There might be multiple or no matches.

 

[tiredofit]

How about

	A	B	C	D	E	F	G	H	I
Table1	Table2
Field1	Field2	Date	Pass	Field1	Field2	Date
a	Y	a
b	N	c
c	Y	d
d	Y	e
e	Y	b

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]05/01/2010[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]04/01/2010[/TD]

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

[TD="align: right"]06/01/2010[/TD]

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

[TD="align: right"]03/01/2010[/TD]

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

[TD="align: right"]07/01/2010[/TD]

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

[TD="align: right"]04/01/2010[/TD]

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

[TD="align: right"]08/01/2010[/TD]

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

[TD="align: right"]03/01/2010[/TD]

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

[TD="align: right"]09/01/2010[/TD]

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

[TD="align: right"]06/01/2010[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]D3[/TH]
[TD="align: left"]=IF(C3>INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0)),"Y","N")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks, that'll do nicely!

I have already written a VBA solution but am dreading it'll take a long time to run when there's a lot of data.

So with your solution, I assume I'll just copy the formula down to as many rows as data I have?

 

[Fluff]

I assume I'll just copy the formula down to as many rows as data I have?

That's right
But you will also need to adjust the ranges first