-1

What is the logic of the DGET function with multiple criteria keys? I have made two small examples to investigate:

Case 1: we get a value by Date type key only - it works fine.

enter image description here

Case 2: same data, but we add a String type key. Here we have an error!

enter image description here

I agree, there are 2 "М000001735" strings in column B ("Инв" field). But there are also 2 keys to get a unique row in this case. What is wrong? Can I use DGET function for compound keys?

player0
  • 124,011
  • 12
  • 67
  • 124

2 Answers2

3

The table should not contain any duplicates. If there are duplicates, you should remove that by using the UNIQUE or SORT function that you can use within the DGET. Because if there are multiple matches, DGET will return an error. The DGET formula returns #VALUE! error if the criterion doesn't match. So you should wrap Google Sheets DGET formula with an IFERROR. Duplicates in the criteria/lookup column are not allowed. This causes #NUM! error in DGET.


if you run: ={{1;2}\{3;4}}

or run this constellation: ={{1\3};{2\4}}

you will get the same result which means either of these will work:

=DGET(A:C; "Рабочее место"; {{"Инв" \ "Дата"}; {"М000001735" \ DATE(2019; 2; 15)}})

=DGET(A:C; "Рабочее место"; {{"Дата"; "Инв"}; {DATE(2019; 2; 15); "М000001735"}})

=DGET(A:C; "Рабочее место"; {{"Инв"; "М000001735"} \ {"Дата"; DATE(2019; 2; 15)}})

0

demo spreadsheet

note: make sure you use Russian Locale in Spreadsheet Settings

_____________________________________________________________

alternatives to DGET:

=QUERY(A:C; "select C where A = date '2019-2-15' and B = 'М000001735'"; 0)

=FILTER(C:C; A:A=DATE(2019; 2; 15); B:B="М000001735")

=VLOOKUP(VLOOKUP(DATE(2019; 2; 15); A:B; 2; 0); B:C; 2; 0)

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you for very good alternatives (especially `FILTER` case). But my question is about DGET syntax and correct understanding. I still have no ideas how to solve my initial task directly. – Александр Ермолин Jun 10 '19 at 07:22
  • No problem - [this one](https://docs.google.com/spreadsheets/d/1K93X6RehAVe4bOEDPzgekeuj1oxBUYoYsw0QHNQVICs/edit?usp=sharing). In addition, I have noticed, that DGET is sensitive for the criteria order - see **Short!G1** cell with the working formula. But if I append data into A:C to have duplicate keys (see "Long" sheet), the formula still works. Very inevident behavior! – Александр Ермолин Jun 10 '19 at 08:17
  • Sorry, but recommended `=DGET(A1:C; "Рабочее место"; {{"Инв"; "M00001735"}\{"Дата"; DATE(2019; 2; 15)}})` returns #ERROR! – Александр Ермолин Jun 10 '19 at 08:35
  • Great, thank you very much for the demo! The only note is about **E4** cell: this variant does not work... after replacing `DATE(2019; 2; 15)` by `DATE(2018; 10; 19)`. We are very close to understand the reason. – Александр Ермолин Jun 10 '19 at 12:20
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/194698/discussion-between---and-player0). – Александр Ермолин Jun 10 '19 at 12:27
0

It helps if an OP explains what they are trying to achieve (as well as what problem they encountered, and where, data sample in text format and so forth).

Please try:

=DGET(A$1:C;"Рабочее место";{{"Инв";"M00001735"}\{"Дата";DATE(2019;2;15)}})

Your 'default' separator is ; hence in your case this does not serve to stack arrays horizontally.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    What is "OP"? I have tried your formula and got #VALUE! errror. Then I have used `=DGET(A$1:C;"Рабочее место"; {{"Инв";"Дата"}\{"М000001735";DATE(2019;2;15)}})` and got #VALUE! again. I have replaced back slash with semicolon - same result. But when I placed compound criteria into 4 cells range and inserted range reference into the formula, it works! So, what is the correct syntax without range reference? – Александр Ермолин Jun 10 '19 at 06:53