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:
Thanks.

Just one further question: Is there any significance to the |?

Could use something else or nothing at all?
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
It's just a delimiter for safety.
With the data you posted you could get rid of it, but if you have data like

Book1
AB
1212345
1312345
1412345
1512345
Sheet1


Without the delimiter all of them would be treated as the same value.
You can change the | to any character you like, as long as it's not in your data
 
Upvote 0
It's just a delimiter for safety.
With the data you posted you could get rid of it, but if you have data like
AB
1212345
1312345
1412345
1512345

<tbody>
</tbody>
Sheet1



Without the delimiter all of them would be treated as the same value.
You can change the | to any character you like, as long as it's not in your data


Thanks again.

The more I look at your solution, the less I understand it (even though it's returning what I want)!

My understanding of the INDEX function is the following:

If in column A, I have a,b,c in cells A1 through to A3 and in cell B1 I type:

Rich (BB code):
=INDEX(A1:A3,2)

I get the value of b because it's the second value in the specified range.

Alternatively if I typed in cell B1:

Rich (BB code):
=INDEX(A1:A3,1)


I get a.

But in your formula, I noticed your last argument was 0.

If I did that to my example here, it depends which cell I am typing it in.

If I typed in cell B1:

Rich (BB code):
=INDEX(A1:A3,0)


I get b.

If I typed it in A1, I get a.

From what I understand, if the last argument is 0, then it will return which row you're typing it in.

Am I correct?

But when I tried it with your formula, ie:

Rich (BB code):
=INDEX($G$3:$G$7&"|"&$H$3:$H$7,0)



regardless of which row I'm typing it in, I get the same result:

Rich (BB code):
1|a

Can you please explain the significance of the last argument of the INDEX function and in particular, when it is a 0.

Thanks

<strike>
</strike>
 
Last edited:
Upvote 0
The 0 means that it will return an array of all the rows.

If you select a cell with the formula & click on the Evaluate Formula button, you can step through the formula seeing what it does.
 
Last edited:
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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