Using Queries in Excel

[Johan Nordberg]

What are the best practices for using database-like queries in Excel.
Let's say you wish to join to sheets together och view a subset of
columns in a third sheet.

I've tried several different methods, but I dont think any of them are
completely good. I've used VLookup, Index, MS Query. (MS Query must be
the must forgotten MS product in history. It's like a time machine
back to Windows 3.11)

I've also tried alot of different methods for searching a range, based
on more than one criteria, and display the result, either a single
value or a sum based on several rows. Here i've tried the D-functions
and Array formulas. The D-functions is not that flexible because you
cannot specify the criterias inline as an array, it must be a
reference to cells, which makes it difficult to manage many DGET with
similar filters.

What are you best practices for doing stuff that is really basic
database-stuff in Excel?

Cheers // Johan

 

[Anya]

Have you tried sumif and countif?
I often combine several of those in one formula.

A combination of index and match is also a good way to
look for a value in a table-like dataset. For example, the
data is in the range A3:I11 and you'd like to find a value
based on criteria specified in cells K4 (for the row value)
and L3 (for the column value), then use this formula:
=INDEX(B4:I11,MATCH(K4,A4:A11),MATCH(L3,B3:I3))

 

[Johan Nordberg]

Thanks for your reply. I agree that both CountIf and SumIf are very useful
functions, but not in these cases. For example, SumIf only accept one
criteria, I often need more than one criteria. This leaves me to Array
Formulas.

However, sometimes you do not know how big results you'll get from a query,
so what I really would like is a more dynamic way. For example, if I query a
list for all rows that match a critiera, I must either know how many rows my
result will be, or play it safe my adding extra rows for my resulting Array
formula. As a programmer I think this is a bad solution.

MS Query is the best alternative I've found, but MS Query is a pain to work
with and it often eat up all my memory, taking 100% cpu power and then
hangs.

// Johan