Excel vlookup incorporating SQL table

Question

I have an Excel spreadsheet which I use to calculate the cost of a product we sell, (happens to be car insurance). This product is calculated based on various pieces of information; the customers age, postcode, vehicle, occupation, etc etc.

I enter information onto the main tab of my spreadsheet and then perform lots of vlookup formulas to return the relevant figures from other tabs of the spreadsheet. An example of one of my vlookups is:

=VLOOKUP(G1,RatesInsurerX!A36:B986786,2,FALSE)

So this looks up the value I enter into cell G1 an matches it to the data found on the workbook tab called RatesInsurerX.

As you can see this particular table contains nearly 1 million rows and excel is starting to struggle so I was wondering if there is a way of doing exactly the same thing only performing the lookup against a SQL table rather than the RatesInsurerX workbook?

Answer 1

Good news is you can do this without VBA, quite a few steps though as follows:

1 . First add a new sheet, call this something meaningful like PoscodeLookup.

2 . Next go to Data and select Other Sources and Microsoft Query:

3 . Next select (or create) an ODBC data source that can connect you to your database asd select this (you may need to enter user/pass).

4 . The query designer will ask you to select a table, just click close.

5 . Next select the SQL button:

6 . Now enter the SQL query to get the single value you need, using an example postcode eg:

SELECT TOP 1 [Value] FROM [MyTable] WHERE [Postcode] = 'AB12 1AA';

7 . Now hit OK and OK which should return a single value in the window.

8 . Click on Return data in the toolbar:

9 . Now back in Excel hit properties in the prompt:

10 . Now change the post code you entered into a ? in the definition tab:

11 . Hit OK and OK again and it will prompt for a value, enter a valid postcode and hit enter, this should now give you the value you want in cell A1.

12 . Now click in the cell A1 and go to Data > Properties then Connection properties:

13 . Now in the definition tab you have a Parameters button at the bottom, in here you can fill out as below:

Note, the target cell is the cell where you enter the postcode, tick the refresh box if you want this to re-run the query when the post code changes.

That should be it, let me know if it doesn't work.

Answer 2

You can. In the VBA of the sheet, create a function like so:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

and in it's body

• Check the Target is the cell you want to base your lookup on (G1)
• If it is, get the data from the DB and put it where it needs to go

Cheers -