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 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!

You are a star - you don't know what a big help this video has done!!!!!!!!!! 🙏🙏🙏

THANK YOU , THANK YOU, THANK YOU. Much love from Malaysia.

Ive been looking for this solution for a while. Thanks a lot

Thanks for posting! Very interesting!

Awesome! worked beautifully, great explanation. much better than concatenating strings before doing vlookup

@ExcelGoogleSheets

4 жыл бұрын

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.

Very useful and interesting as usual, thank you

Great video, it helped me out. Thanks.

Great Video! I've tried with vlookup looking for multiple columns but not successful. I have learned something new. Thanks.

Excellent !

Very very helpful, Thanks

This is also perfect for finding dates between date ranges.

Thank you. Very helpful.

Great video. How do you get the array bracket nested in the formula

Nice to know about the comparison operators! Thanks

Thank You very much for great explanation 3:00

Is there a way to build in a does not equal aspect to the array? Or a way to filter out more to prevent the duplicate error?

very usefull, thanks

Thank you very much

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?

This was helpful, thank you

@ExcelGoogleSheets

2 жыл бұрын

Cool!

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

@TheMrNigri

11 ай бұрын

Me salvou!!

@MrGuilherme1100

9 ай бұрын

Muito obrigado!!

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?

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

I appreciate your videos! Thank you!

Thank You

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.

Hello, how would I look up the value with a close match and not an exact match

Hello sir Can you tell me how to make a individual employee training history record from a bulk training record

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

@ExcelGoogleSheets

3 жыл бұрын

Sounds like DGET should handle it.

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?

@ExcelGoogleSheets

4 жыл бұрын

I don't think you can.

@SunilKumar-ln2vr

3 жыл бұрын

@@ExcelGoogleSheets is there any way to do with array formula when we have multiple criteria.

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.

Brilliant! Can we have the table from different google sheets? How should we do that? Thank you in advance.

@santiibene2311

4 жыл бұрын

yes, just use importrange, like this =DGET(IMPORTRANGE($O$2;$O$3);"Marca temporal";{"DNI","Examen";A$11,M$11})

can we use arrayformula with the Dget function?

🙏🏻

Can you use DGet inside an arrayformula?

Thank you!

DGET can link data form another file in googledrive?

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.

@ExcelGoogleSheets

3 жыл бұрын

try =date(left(a1),mid(6,2),mid(9,2))

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})

@tomascastrillon567

4 жыл бұрын

The solution you prupose in the comment section works fine if you do one cell at a time, but for dragging it along doesn't work.

@ExcelGoogleSheets

4 жыл бұрын

The formula looks fine. Can't tell without seeing you data.

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.

@cdelgato19

4 жыл бұрын

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!

please i want help in gs.code in google sheet . i can't send a message to you

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

@ConsulthinkProgrammer

4 жыл бұрын

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

@ExcelGoogleSheets

4 жыл бұрын

Yes, absolutely. Instead of "Distance" use {"Distance", "Cost"}

@ConsulthinkProgrammer

4 жыл бұрын

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.

@ExcelGoogleSheets

4 жыл бұрын

Yes, it won't.

while using importxml, there is lot of loading issue in the sheet, Kindly suggestion

Why am I not able to use semicolon in excel. It auto corrects full Colon and Throws an error #value!

Thank's !

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?

@ExcelGoogleSheets

3 жыл бұрын

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.

@TheEmjay19

3 жыл бұрын

@@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.

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?

@ExcelGoogleSheets

2 жыл бұрын

Use VLOOKUP instead

@SatrioBudiDharmawan

2 жыл бұрын

@@ExcelGoogleSheets multiple criteria? Vlookup can do it?

@ExcelGoogleSheets

2 жыл бұрын

Watch the last part of this video for multiple criteria match kzread.info/dash/bejne/aJervNGElL3OaZc.html

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

4 жыл бұрын

No. Share an example.

@jaguandebruno

4 жыл бұрын

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

@ExcelGoogleSheets

4 жыл бұрын

Share an example sheet. Comments don't help/

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.

@ExcelGoogleSheets

5 жыл бұрын

Share an example sheet please.

@Mohammedbasim

3 жыл бұрын

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.

This is good video but please sir always make video on script.

@YoungHumanClub

4 жыл бұрын

why? some learn differently than others. I really enjoy his video style of teaching

Hello sir I have dought can you please clear my dought

@RajeshKumar-le1kb

3 жыл бұрын

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

My DGET function keeps coming back with #VALUE - could this be because the sheets I am pulling from are queries?

@ExcelGoogleSheets

5 жыл бұрын

No, it should work.

you are fucking hero

@ExcelGoogleSheets

Жыл бұрын

:)

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!

@MrVictorTrincado

4 жыл бұрын

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!

@ExcelGoogleSheets

4 жыл бұрын

kzread.info/dash/bejne/np6qpJSaoqbZfrQ.html

DGET IS SHOWING MORE THAN ONE MATCH FOR 160 MEDIUM 160 MEDIUM BLUE it is seeing these two as the same item. clearly these two are different !!

@ExcelGoogleSheets

4 жыл бұрын

change your criteria instead of A1 to "="&A1

@Mohammedbasim

4 жыл бұрын

@@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.

@ExcelGoogleSheets

4 жыл бұрын

I'm pretty sure if you make the change I suggested that will fix your issue.

@Mohammedbasim

4 жыл бұрын

@@ExcelGoogleSheets (DGET(sale!$D$2:$F,"QTY",{"MONTH","ITEM";C$1,$B16}) This is my criteria..which one should i be changing?

@Mohammedbasim

4 жыл бұрын

@@ExcelGoogleSheets THANK YOU ! ITS WORKING !!

Pro tip: Cost in English is pronounced "Cahst" not "Coast".

getting Num error when i am using the same formula for my data DGET($A$1:$F$11935,"Qualified/Disqualified",{"Email ID","Month";I45,J45})

@ExcelGoogleSheets

Жыл бұрын

Num error typically means you have more than one match.