2

I am running into a weird DGET error in Google Sheets.

in one table, I have the following entries:

Column A        Column B
Horse           Wins
Daedalus I      15
Daedalus II     12
Panacea I       10
Panacea II      9

when I use

=dget(A1:B5,"Wins",{"Horse";"Daedalus I"})

I get a #NUM! error. However, if I change the table from "Daedalus II" to "Daedalus 2", the error is gone.

  • Is this the expected behavior or a bug in Google Sheets?

  • Any way to circumvent this without changing the table?

1

@James/mreighties posted an answer on Google, that I am copying here for future reference:

Hello,

I may have found the answer at this site...

https://infoinspired.com/google-docs/spreadsheet/exact-match-in-database-functions-in-google-sheets/

...and changed your formula to this...

=dget(A1:B5,"Wins",{"Horse";"=Daedalus I"})

...and it works for me. Take a look at my testing sheet...

https://docs.google.com/spreadsheets/d/1EoTytnKAIrrRmRwPX9k2xdEKeJMk4i5O3BtIXjaVSc4/edit?usp=sharing

...where in F2 is this formula...

=dget(A1:B5,"Wins",{"Horse";"=Daedalus I"})

...which hardcodes the criteria in the formula.

And in H2 is this formula...

=dget(A1:B5,"Wins",{"Horse";"="&D2})

...which looks at the choice made from the drop-down in D2 to use as the criteria in the formula.

James :)

1
  • Something I didn't know. Still. This will work in your case where the values present are NOT repeated. Will fail though if they are because "DGET is the only database function that does not aggregate" – marikamitsos Mar 5 at 18:06
0

From the Google official support site

DGET is the only database function that does not aggregate – it will return an error if the filtered data has either no matching elements or more than one matching element.

So yes. This is the expected behaviour.

As your best alternative would be to use the QUERY function

=QUERY(M1:N,"select N where M='Daedalus I' ",1)

OR

=QUERY(M11:N16,"select N where M matches 'Lucky M' ",1)

enter image description here

Of course you will argue
"But Daedalus I is not the same as Daedalus II"
Correct.

What happens with DGET?
You should think of DGET as an alternative of the where QUERY clause.
The sting comparison operators would be matches, contains, like, starts with, ends with.

DGET is an alternative for the starts with in a QUERY
But DGET does NOT aggregate.
Thus the error.


EDIT (after taking a look at your sheet)

You can also use

=QUERY(A1:B,"select B where A matches '"&D2&"' ",0)

enter image description here

2
  • Thank you! See also a way to force DGET to get exact matches in my own answer, above – Oren Pinsky Mar 5 at 17:42
  • Thank you. Something I didn't know. – marikamitsos Mar 5 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.