View Full Version : Use an Access query to simulate an Excel VLOOKUP between two tables

Spacebiscuits

11-07-2010, 11:02 AM

Does anyone know how to set up a query in Access to simulate an Excel VLOOKUP between two tables?

For example, I have two tables:

A B C

1 Table1 Table2

2 1 4.56

3 2 6.60

4 3 2.39

5 4 3.18

6 5 1.53

In Excel, I'd enter this formula into cell C2 and copy it down:

=VLOOKUP(B2,Table1,1)

resulting in:

A B C

1 Table1 Table2 Result

2 1 4.56 4

3 2 6.60 5

4 3 2.39 2

5 4 3.18 3

6 5 1.53 1

I'd like to set up a VLOOKUP-like query in Access which would return the results as shown column C. How do I do this?

ADoubleDot

11-07-2010, 11:30 AM

Select Query add the two tables. In the gui interface, drag the variable you want to do your so-called vlookup on from one table to the other. This is called a join. This is a common practice in database software such as SQL or access. Pull the variables you want in the result down to the table below.

Here you need to be careful, by default access selects inner join. This means it will only keep records where your joining variable(s) are on both tables. If you double-click the joining line, you can switch it to other types of joins. Good luck to you.

Anonymouse

11-07-2010, 03:03 PM

Need to use the INT function before doing the join

Spacebiscuits

11-07-2010, 04:58 PM

Thank-you for the responses, but what I am looking for is a way to replicate VLOOKUP. In particular, the situation analogous to when the range lookup argument is set to the default value (i.e., TRUE or omitted).

ADoubleDot

11-07-2010, 05:37 PM

Hmm, if you built the table such that there were three essential columns, max min and the lookup value. Then, in the upper right hand corner, switch from design view to sql view and change the "on" condition to

min <= myLookupValue <= max

Not sure if that works for you. I don't really understand your example.

Spacebiscuits

11-07-2010, 06:06 PM

I don't really understand your example.I want to obtain the same result given the same inputs in Access as what I'd get if it was done in Excel using VLOOKUP.

In Excel, provided the Lookup Array is sorted in ascending order and the Range Lookup argument is set to the default value, a VLOOKUP will is the row corresponding to the maximum Lookup Array value which is less than or equal to the Lookup Value. Adding a second Max column assumes I know what the maximum value would be. Using INT solves the spefic example I gave, but it doesn't tell me how to replicate VLOOKUP in Access since it's a solution specific to what is just an illustrative example.

far_side

11-07-2010, 06:34 PM

The max column is the same value as the min from the next row. Vlookup works exactly the same with the implied max value. The last value should be set to {infinity} unless you want an error catch value. For exact values, the inequalities in the query will decide which row matches. Note this answer is exactly what everyone else has pointed out.

far_side

Table1

Min Max Result

1 2 1

2 3 2

3 4 3

4 5 4

5 999 5

Table2

Value

4.56

6.60

2.39

3.18

1.53

Query1

SELECT Table2.values, Table1.result

FROM Table1, Table2

WHERE (((Table2.values)>[table1].[min] And (Table2.values)<=[table1].[max]));

Spacebiscuits

11-07-2010, 06:42 PM

Vlookup works exactly the same with the implied max value.How do I determine the implied max value?

far_side

11-07-2010, 09:10 PM

How do I determine the implied max value?

Read the first sentence in my comment above :roll:

Spacebiscuits

11-07-2010, 09:31 PM

Read the first sentence in my comment above :roll:Yes, thank-you, I saw that the first time.

What's the process for finding the value in the "next row"? And for coding "infinity". In other words, if I already knew that I wouldn't have started this thread.

SpaceActuary

11-08-2010, 09:29 AM

You might have to rename some of the column names, but I think this sort of thing should work.

SELECT [temp].table2.Field1, Max(table1.field1) AS 'vlookup'

FROM [SELECT *

FROM table2, table1

WHERE table2.field1 >= table1.field1]. AS [temp]

GROUP BY [temp].table2.Field1;

The sub-query (the second "SELECT" inside parentheses) creates a cartesian product of the two tables, then filters out the records where the lookup column is less than the returned value.

The outer query takes those results, and selects the highest value of the "looked up" value for each row in the original table.

Is that what you're looking for?

Spacebiscuits

11-08-2010, 09:42 AM

Is that what you're looking for?Yes it is. It's exactly what I was looking for. Thanks!

Yes it is. It's exactly what I was looking for. Thanks!

Would you mind explaining exactly why it is that you want to do this?

Spacebiscuits

11-08-2010, 10:13 AM

Would you mind explaining exactly why it is that you want to do this?What are you wanting to know? Why someone would use VLOOKUP (or it's equivalent in other programs) that way or why I'm doing this in Access rather than Excel?

Spacebiscuits

11-08-2010, 11:18 AM

Why in Access?Instead of Excel? Too many records.

Instead of something else? Access is the tool I'm using.

vBulletin® v3.7.6, Copyright ©2000-2017, Jelsoft Enterprises Ltd.