Approximate match / vlookup

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,109
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
 
Last edited:

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,109
Thanks but how can I adapt it for my particular requirement?

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,845
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Apr 11, 2013
Messages
1,109
Table1Table2
Field1Field2DateField1Field2Date
1a05/01/20101a04/01/2010
2b06/01/20103c03/01/2010
3c07/01/20104d04/01/2010
4d08/01/20105e03/01/2010
5e09/01/20102b06/01/2010
Output
Field1Field2DatePass
1a05/01/2010Y
2b06/01/2010N
3c07/01/2010Y
4d08/01/2010y
5e09/01/2010Y

<tbody>
</tbody>

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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,845
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Table1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Table2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Field1</td><td style=";">Field2</td><td style=";">Date</td><td style=";">Pass</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Field1</td><td style=";">Field2</td><td style=";">Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1</td><td style=";">a</td><td style="text-align: right;;">05/01/2010</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";">a</td><td style="text-align: right;;">04/01/2010</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">2</td><td style=";">b</td><td style="text-align: right;;">06/01/2010</td><td style=";">N</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style=";">c</td><td style="text-align: right;;">03/01/2010</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">3</td><td style=";">c</td><td style="text-align: right;;">07/01/2010</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style=";">d</td><td style="text-align: right;;">04/01/2010</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">4</td><td style=";">d</td><td style="text-align: right;;">08/01/2010</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style=";">e</td><td style="text-align: right;;">03/01/2010</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">5</td><td style=";">e</td><td style="text-align: right;;">09/01/2010</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style=";">b</td><td style="text-align: right;;">06/01/2010</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=IF(<font color="Blue">C3>INDEX(<font color="Red">$I$3:$I$7,MATCH(<font color="Green">A3&"|"&B3,INDEX(<font color="Purple">$G$3:$G$7&"|"&$H$3:$H$7,0</font>),0</font>)</font>),"Y","N"</font>)</td></tr></tbody></table></td></tr></table><br />
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,109
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

Well-known Member
Joined
Apr 11, 2013
Messages
1,109
How about

ABCDEFGHI
1Table1Table2
2Field1Field2DatePassField1Field2Date
31a05/01/2010Y1a04/01/2010
42b06/01/2010N3c03/01/2010
53c07/01/2010Y4d04/01/2010
64d08/01/2010Y5e03/01/2010
75e09/01/2010Y2b06/01/2010

<tbody>
</tbody>
Sheet1

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

<tbody>
</tbody>

<tbody>
</tbody>
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?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
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
 
Last edited:

Forum statistics

Threads
1,086,116
Messages
5,387,923
Members
402,088
Latest member
poppa57

Some videos you may like

This Week's Hot Topics

Top