Approximate match / vlookup

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Thanks but how can I adapt it for my particular requirement?

Besides, I am NOT allowed to sort the data beforehand.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
How about


Book1
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
Sheet1
Cell Formulas
RangeFormula
D3=IF(C3>INDEX($I$3:$I$7,MATCH(A3&"|"&B3,INDEX($G$3:$G$7&"|"&$H$3:$H$7,0),0)),"Y","N")
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
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