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

 

[tiredofit]

Thanks.

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

Could use something else or nothing at all?

 

[Fluff]

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
	A	B
12	1234	5
13	123	45
14	12	345
15	1	2345
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

 

[tiredofit]

It's just a delimiter for safety.
With the data you posted you could get rid of it, but if you have data like

	A	B

<tbody>
[TD="align: center"]12[/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]345[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2345[/TD]

</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:

=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:

=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:

=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:

​

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

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

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>}}​

 

[Fluff]

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.

 

[tiredofit]

Great, that's cleared things up.

 

[Fluff]

You're welcome & thanks for the feedback