DGET fails to lookup properly when two entries are similar

Question

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?

Answer 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 :)

Answer 2

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)

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)