Lookup with Multiple Criteria - VLOOKUP, MATCH solved with DGET - Google Sheets
Вставка
- Опубліковано 24 сер 2024
- 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
You are a star - you don't know what a big help this video has done!!!!!!!!!! 🙏🙏🙏
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!
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.
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
Me salvou!!
Muito obrigado!!
Ive been looking for this solution for a while. Thanks a lot
Great Video! I've tried with vlookup looking for multiple columns but not successful. I have learned something new. Thanks.
Thank You very much for great explanation 3:00
THANK YOU , THANK YOU, THANK YOU. Much love from Malaysia.
Nice to know about the comparison operators! Thanks
Very useful and interesting as usual, thank you
Thank you. Very helpful.
Excellent !
Awesome! worked beautifully, great explanation. much better than concatenating strings before doing vlookup
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.
Great video. How do you get the array bracket nested in the formula
Very very helpful, Thanks
This was helpful, thank you
Cool!
Hello, how would I look up the value with a close match and not an exact match
very usefull, 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.
Thank You
Great video, it helped me out. Thanks.
This is also perfect for finding dates between date ranges.
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?
Thanks for posting! Very interesting!
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?
Why am I not able to use semicolon in excel. It auto corrects full
Colon and Throws an error #value!
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
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?
Brilliant! Can we have the table from different google sheets? How should we do that?
Thank you in advance.
yes, just use importrange, like this =DGET(IMPORTRANGE($O$2;$O$3);"Marca temporal";{"DNI","Examen";A$11,M$11})
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?
I don't think you can.
@@ExcelGoogleSheets is there any way to do with array formula when we have multiple criteria.
while using importxml, there is lot of loading issue in the sheet, Kindly suggestion
Hello sir Can you tell me how to make a individual employee training history record from a bulk training record
Thank you very much
Can you use DGet inside an arrayformula?
can we use arrayformula with the Dget function?
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
Sounds like DGET should handle it.
DGET can link data form another file in googledrive?
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!
I appreciate your videos! 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, 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.
try =date(left(a1),mid(6,2),mid(9,2))
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?
Use VLOOKUP instead
@@ExcelGoogleSheets multiple criteria? Vlookup can do it?
Watch the last part of this video for multiple criteria match ua-cam.com/video/6cwZoKdZh94/v-deo.html
please i want help in gs.code in google sheet . i can't send a message to you
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.
Share an example sheet please.
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 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?
No. Share an example.
@@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
Share an example sheet. Comments don't help/
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 :)
Yes, absolutely. Instead of "Distance" use {"Distance", "Cost"}
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.
Yes, it won't.
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})
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.
The formula looks fine. Can't tell without seeing you data.
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.
Hello sir I have dought can you please clear my dought
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
Thank you!
you are fucking hero
:)
Thank's !
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 !!
change your criteria instead of A1 to "="&A1
@@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.
I'm pretty sure if you make the change I suggested that will fix your issue.
@@ExcelGoogleSheets (DGET(sale!$D$2:$F,"QTY",{"MONTH","ITEM";C$1,$B16})
This is my criteria..which one should i be changing?
@@ExcelGoogleSheets THANK YOU ! ITS WORKING !!
My DGET function keeps coming back with #VALUE - could this be because the sheets I am pulling from are queries?
No, it should work.
This is good video but please sir always make video on script.
why? some learn differently than others. I really enjoy his video style of teaching
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!
ua-cam.com/video/ljvB2arCsNQ/v-deo.html
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})
Num error typically means you have more than one match.