Google Sheets – DGET Fails to Lookup Similar Entries

formulasgoogle sheets

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?

Best Answer

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

Formula

=ARRAYFORMULA(TRIM(SPLIT(JOIN(" ,", A1:A4,C1:C4), ",")))

Explained

You already showed us that the SPLIT function ignores empty cells, therefore I choose this string as delimiter: " ,". The intermediate result of the JOIN function, looks like this:
enter image description here

I choose the delimiter for the SPLIT function to be this: ",". The intermediate result of the SPLIT function, looks like this:
enter image description here

Now, however, we have to deal with the extra added white spaces. Therefore I used the TRIM function, to remove all trailing or leading white spaces (in combination with the ARRAYFORMULA). Compare the LEN values of each intermediate result.

Result

enter image description here

Example

I've created an example file for you: SPLIT to preserve blank cells

Google Sheets – How to Grant Permission to Self-Defined Functions

Custom functions can't call services that require authorization. Source: https://developers.google.com/apps-script/guides/sheets/functions.

The way to overcome the problem is to use another way for calling the function, like assign the script to a custom menu, an image, an installable trigger or by running it from the Script Editor.

Best Answer

Related Solutions

Google-sheets – “Split” delimited string, preserving blank entries

Formula

Explained

Result

Example

Google Sheets – How to Grant Permission to Self-Defined Functions

Related Topic