Is there any way to use SQL directly inside Excel to return a value from a
table to a cell, both in the same worksheet, without using ODBC?

I seem to remember being able to do this in 123.

I would prefer to do this by entering the SQL into a cell, but a VBA
function would work also.

Any help would be appreciated. I'm a mainframe guy, a little lost in Excel
trying to help my daughter.

In case you wonder why: I want to look up a value in a table that is 3 cols
by 30,000 rows. I need to match two columns and return the third. I tried
to convert the table to two dimensions with the pivot table wizard, but
although it seemed to work, it discarded many chunks of data. I know SQL a
lot better than I know Excel's lookup functions. This needs to run on a
version of Excel without the ODBC add in, and no authority to ad it in.

Hi Biff,

From my experience, using SQL queries on an open workbook can cause issues.
It is possible, but I think you can accomplish this in other ways. The DGET
worksheet function may do what you want. Or you could use an array
function. If you look in help under "DGET Worksheet Function", you will see
a pretty good example of how to use it.

Using an array function, you could do something like this:

=MAX((A2:A100="Jake")*(A2:B100="Marx")*(C1:C100))

*Array formulas must be entered with Ctrl+Shift+Enter

This array formula will return the unique ID (residing in column C) of an
employee named "Jake Marx" (where first name resides in column A and last
name resides in column B). If there are multiple records matching your 2
criteria, then this would not be a good approach (but neither would DGET),
as it would return the highest ID of the matching records. No matching
records will result in a zero value.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Biff Canlett wrote:
Is there any way to use SQL directly inside Excel to return a value
from a table to a cell, both in the same worksheet, without using
ODBC?

I seem to remember being able to do this in 123.

I would prefer to do this by entering the SQL into a cell, but a VBA
function would work also.

Any help would be appreciated. I'm a mainframe guy, a little lost in
Excel trying to help my daughter.

In case you wonder why: I want to look up a value in a table that is
3 cols by 30,000 rows. I need to match two columns and return the
third. I tried to convert the table to two dimensions with the pivot
table wizard, but although it seemed to work, it discarded many
chunks of data. I know SQL a lot better than I know Excel's lookup
functions. This needs to run on a version of Excel without the ODBC
add in, and no authority to ad it in.