Even after immense research, I couldn’t see anybody using a DGET array formula to run down a column in Google Sheets.
There are questions regarding the same on popular Google Sheets forums that are either unanswered or provided alternative solutions using QUERY, FILTER, or VLOOKUP.
It’s because the database function DGET does not support a criteria column in Google Sheets. So you won’t expect to get an expanding array result using DGET in its standard form.
Syntax:
DGET(database, field, criteria)But I have a workaround using DGET itself to make it work as an Array Formula to run down a column without dragging it down.
For example see the below formula. It uses a single criterion in cell G2.
=DGET($A$1:$E,"Maths",{"First Name";G2})What about G2:G as below?
=ArrayFormula(DGET($A$1:$E,"Maths",{"First Name";G2:G}))You will get the error #NUM! The tooltip will say;
More than one match found in DGET evaluation.
Similarly;
This one will work.
=DGET($A$1:$E,"Maths",{"First Name","Last Name";G2,H2})But not this one.
=ArrayFormula(DGET($A$1:$E,"Maths",{"First Name","Last Name";G2:G,H2:H}))In this tutorial, you can learn how to write a DGET Array Formula in Google Sheets that runs down a column to produce an expanded result.
Of course, we can use Vlookup for the same purpose. I’ll share that formula also with you.
DGET Array Formula for Expanding Row Results in Google Sheets
In the following database (dataset), I want to find the marks of “Rita”, “Paula”, and “Joan” (G2:G) for the subject “Maths”.
As you can see, I have used three database formulas for the same in cell range H2:H4.
Note:- On the image, I have used the FORMULATEXT function in I2:I4 to show you the formulas in the range H2:H4. As you can see, I have used the ArrayFormula function with the formulas. It’s not necessary.
That means I have dragged (copy-pasted) the below H2 formula down the column.
=DGET($A$1:$E,"Maths",{"First Name";G2})The DGET in H2 can’t expand itself with the help of the ArrayFormula function. That means replacing the cell reference G2 with G2:G is not fruitful.
We should rewrite the formula. Here are the logic and steps.
Logic
It is important to note that the Google Sheets DGET function takes/accepts multiple “field” numbers/labels.
Here is an example.
=ArrayFormula(DGET($A$1:$E,{"Maths","Chemistry"},{"First Name";G2}))In this formula, the two fields are “Maths” and “Chemistry”.
We can make use of this capability to write the DGET array formula in Google Sheets.
How?
We will transpose (change the orientation) the database (range) to make the criteria to use as the fields and vice versa.
You can learn that below.
Formula and Explanation
DGET Array Formula (Row-Wise Expanding):
=ArrayFormula(IFERROR(dget(TRANSPOSE(A1:E),G2:G,{"First Name";"Maths"})))
I have transposed the database. So, I have used the criteria as the fields and the fields as the criteria.
Please note that to get the marks from another column, for example, the “Chemistry” column, insert the same formula in cell I2 and then replace “Maths” in the I2 formula with “Chemistry”.
Formula Explained
I have already explained how to write a DGET array formula in Google Sheets above. Here are more details.
database – TRANSPOSE(A1:E)
Our actual database is in the range A1:E. Instead of using it, we have used the transposed range A1:E using the function TRANSPOSE.
field – G2:G
In our transposed database, the first row becomes the first column, the second row becomes the second column, and so on (please refer to the screenshot below).
So, our actual criteria (column) will become fields (row). So that, I have used the criteria column G2:G as the fields.
criteria – {"First Name";"Maths"}
Here our actual fields become the criteria.
It will be as follows (while testing only use cell range A1:E8 within TRANSPOSE to avoid issues)
Can you provide me the Vlookup alternative to the above formula?
Why not! Here you go.
=ArrayFormula(IFNA(vlookup(G2:G,A2:E,3,0)))This Vlookup is very easy to use and learn compared to the above DGET array formula in Google Sheets – How to Use Vlookup to Return An Array Result in Google Sheets.
What am I trying to prove then?
We can use the DGET function to return a row-wise expanding array result in Google Sheets.
DGET Array Formula with Criteria in Two Columns in Google Sheets
What about the below non-working formula?
=ArrayFormula(DGET($A$1:$E,"Maths",{"First Name","Last Name";G2:G,H2:H}))Can we make it working by transposing the database as earlier?
The answer is “YES”, but here we should follow a little different approach.
Here is the formula.
=ArrayFormula(IFERROR(dget(TRANSPOSE({A1:A&" "&B1:B,C1:E}),G2:G&" "&H2:H,{"First Name Last Name";"Maths"})))We should combine the criteria columns within the TRANSPOSE (before changing the database orientation) and also the criteria which act as the fields.
I hope, the below illustration may help you to understand this DGET array formula concept clearly.
Please transpose the range as below in your sheet, and you will be able to read the rest of the formula part.
=ArrayFormula(TRANSPOSE({A1:A8&" "&B1:B8,C1:E8}))I seriously recommend the below Vlookup.
=ArrayFormula(vlookup(G2:G&H2:H,{A2:A&B2:B,C2:E},2,0))That’s all. Thanks for the stay. Enjoy!
Resources:
