Learn how to do lookups with multiple conditions using DGET function in Google Sheets. This will solve common issues with INDEX/MATCH or VLOOKUP with multiple criteria & lookup values. #DGET #GOOGLESHEETS
Love your videos! Frequent and consistently good content from you 😄 Little nitpick about value ranges (categories): I use same number both as end of one range and start of the next one - this way I will not lose any values in-between, like 500 and a half, as 500 < 500.5 < 501. I commonly see datetime ranges sliced with leftover values (say, reports for Monday 00:00 -23:59, Tuesday 00:00 - 23:59, etc - losing the last minute of the day). Obviously this is only a problem if your values can at all fall in those cracks between your chosen ranges and you care about them.
Wow this helped me. I was looking to reference an existing address if "Lastname" existed in our member list. =IFERROR(DGET('Memberlist'!$A$1:$L$400,"Address",{Lastname;D2})) Then expanded this across some column headers and many rows. Wow, hundreds of address labels compiled in a minute. THANK YOU!
Make sure you watch the video about database functions in general so that you understand all the little weird details about these functions. Otherwise you may end up getting some funny results.
Can I use this to return a duplicate value (target column: A:A) and output that return value (C:C) based on one cell (B:B)? By the way, how can I reach you out to discuss this in-depth?
Yet another amazing video thank you! I really appreciate it if you could write me the formula to use the same DGET with an Array so it could fill down I've looked everywhere & cant find DGET with an Array formula - I have multiple conditions like above
Thanks for posting. Why in the minute 9.40 we had to use & to reference a cell and in the previous exercise we did not, we just name b2, d2 in the dget function?
can dget work with partial match? The criteria is "Y2019595" and in the database the cell has "Y2019553, Y2019595, Y2019616, Y2019665, Y2019695". Dget cannot find the value. Tried "=" &"Y2019595" but didn't work.
Adicionando que em alguns países como o Brasil, na hora de criar as Matrizes com os colchetes, o comando só funciona usando barra invertida ("\") no lugar da vírgula ... In some countries you have to use "\" instead of commas for the Curly Brackets formula
great video, is it possible to do a more than one dget, I mean if we had a more than just From and To, let say we add first transit, second transit, etc. And it changed the price. Is it possible with the DGET Function or we combined it with IF function? thanks
This is great, but it seems it won't work with dates. I am trying to look up Year - Month in 2 columns in one table and both columns must match in the other table. Error No matches are found in DGET evaluation.
Hey, I didn't know how great DGET function is! However, how would you include DGET into an ARRAYFORMULA? Like you are copying the formula into each row, it would be even more powerful, if you can use ARRAYFORMULA to get that result automatically for each row. But how?
Hello, which function should be used to get the date in format 'DD-MM-YYYY' from the given format which is '2020-04-20T16:30:04+00:00' which is on another Tab in the same work book. Please help.
Great stuff, I used this formula before but for some reason I can't seem to get this one to work: =dget(Lunches!$A$1:$M,"Time",{"Employee Name","WorkDay";$A5,B$3}). I broke it down to just one lookup and it worked but when I add a second criteria I get the #VALUE error.
I figured it out. The WorkDay field was formatted to return the day of the week but in actuality it was a number. I changed the formula and used the workdate field and works perfectly. Great Video!
Hi, I'm running with an error, maybe you can help me, when stated two very similar criteria, in this case K2 and K2Di2 it creates a value error. Any idea how this can be sorted without changing this data? This is the full syntax DGET('Listado precios'!$A$1:$D$41;"PVP";{"Modelo"\"Grupo";C7\G7})
Thank you, this is very informative. Question though, I keep getting -9999 as a result on DGET when it is supposed to be 50.4. Other cells are doing just fine but there are a couple of cells that produce this result. My code for all of the cells is =IFERROR(DGET(GSM!$A$1:$D,"Rainfall",{"Mun","Bar";$C526,$D526}),"") I am wondering why is that?
Watch my video about database functions in general so you understand how matching works. It's possible for example that you have multiple options for Rainfall, like Rainfall Tomorrow, Rainfall Today etc. and database functions have very unusual way of handling text matches.
@@ExcelGoogleSheets Thank you sir, I will watch the video you mentioned... No sir, I only have one column for rainfall... other cells for rainfall outputs are good, however, there are a few that are giving me -9999 outputs instead of the correct ones. I wish I can share with you the document or even just the screenshot.
Can DGET call for two columns like Vlookup with array? I mean, in case with data in your video I want to call "Distance" and "Cost" as a result. How the formula would be like? Thanks :)
Hm, for now-a-while I use Vlookup for handle that problem-question Sir. With my example: My database was some columns in A1 to D4 (Name, Birth Date, Class, Value) My example problem: In cell F1 to I4, I want to input data with two criteria (Name and Birth Date), and recall Class and Value as an output/results. My formula in column H2 is: =ArrayFormula(ifna(VLOOKUP(F2:F4&G2:G4,{A2:A4&B2:B4,A2:D4},{4,5},0))) I created unique_name from column A and B, and from column F and G which is "Name" and "Birth Date", this A2:A4&B2:B4 and F2:F4&G2:G4 (I called it "array-artificial-column") was the key. Again, that's "My own customize Vlookup formula"--that I just created--was inspiring by your youtube lecture video. Thank's a lot Sir :)
Thank's Sir, your advice absolutely work for recall two columns (with ArrayFormula in front of the formula). But, as you said to reply the particular comment in this session, DGET--like OR function--did not support with ArrayFormula to get automatically result for each row.
Sometime it have more than 1 result and when that happen it will throw error: More than one match found in DGET evaluation. How do I force it to take the last or first record?
I have an issue using the VLOOKUP and i think the issue is because the table i am using to search an id code is generated by a formula. Is that a limitation for VLOOKUP and there is any other function to find my data?
@@ExcelGoogleSheets Ok so this is my vlookup formula VLOOKUP(B2,Instructors!I2:J30,2,0) in the table or the boxes are populated with data except for one that is using a formula =ArrayFormula(IF(LEN(B:B),if(ROW(B:B)=1,"ID",TEXT(ROW(A:A)-1,"9900")),)) to generate an ID number every time new data is inserted via Google Forms when i try to search the ID code using vlookup it says that he cant find it
OK THIS WORKS BUT I HAVE A PROBLEM. DGET(JUN!$A$1:$G$1000,"SOLD",{"DATE","ITEM NAME";D$1,$A80}) this is my formula in item name i am facing a problem. it is giving me more than one match found error even though there is only one match. but happend to find what the problem is DATE ITEM NAME 4/6/19 50MM SOCKET DOOR ELBOW 4/6/19 50MM SOCKET DOOR ELBOW (W) here you can see clearly these two are different but it is showing this as an NUM! ERROR. When i delete this item 50MM SOCKET DOOR ELBOW (W) , it is showing value for the the above item (no error). i tried removing the brackets but that doesn't work it still shows the same error.
Yes i have managed to fix it . Dget( data range , "header to pull from", {"header to match " ; "="&cell to match }) Use the formula in the above mentioned way.
I have three criteria what I wanna do that till a1:I1 I have user ID and in every user id have have a work in column user id is in a row what I wanna do who ever user have 1value that left column value I need with particular id
@@ExcelGoogleSheets that is not the problem. My criteria is like that . Somehow it is not understanding the difference . For eg: 160 medium 160 medium blue It is showing this as same But 160 medium blue 160 medium red It is understanding this as different. But not the first one.
Many thanks for your videos! Please help me with this doubt... How do i know what is the criteria separator to spanish version of that function (BDEXTRAER - DGET)? BDEXTRAER(test!A$8:$H;"Folio DTE";{"Folio DTE";"Razon Social";"Monto Total",$I51;$H51;$M51}) Google Sheets return #Error!
I found the answer!!! I saw the light!!! =BDEXTRAER(test!A$8:$H;"Folio DTE";{"Folio DTE"\"Razon Social"\"Monto Total";$I51\$H51\$M51}) If comma is used as decimal separator, then it is necessary to replace with "\" Thanks anyway and please upload more videos!
