It's time to talk about an old school, undercover Excel formula
that helps us solve two LOOKUP problems we can't
easily solve with VLOOKUP. Problem number one, is
looking up a value based on multiple conditions, and number two, is looking up a value to the
left of the LOOKUP range. Now I know I cover a lot of futuristic, superhero formulas like
XLOOKUP and Filter Function that can do all this, but
not everyone is going to get these functions soon. So it's time to take a look back, specifically take a look
at the DGET Excel Function. DGET is a database function. It's easy to use once you know the rules. It can be your go to formula
depending on the situation. So let's take a look. (upbeat music) First of all, we're going
to take a look at using DGET with using one criteria and then we're going to use
DGET with multiple criteria. Here I have a list of
divisions, department and name. What I want to to is
to get a dropdown list for the name and I want
to get the division back. On this side here, I
have the list of names that I'm going to use for that dropdown. So let's quickly insert
the dropdown right here. Click on the cell where we want it. Go to Data, Data Validation, select List. First Source is sitting right
here Control+Shift+Down. Now one thing I'm going to
do is I'm going to turn off the Error Alert because
I want to show you how you can get DGET to work for
approximate matches as well. In our first example, let's
select one person from here, so let's go with Kim West and
let's get the division back for Kim West using the DGET formula. So with DGET we get to do a live filter. The formula is really simple once you know what the syntax is. First one is database. This means that your
dataset has to be organized in a tabular format, which
means that each column needs to have a column header. And the headers are really
important when you use the DGET formula because you
always need to include them. So for database here we
have to include the headers Control+Shift+Down, select
the entire data range. Now if you're going to
be adding data to this, you can include more cells, and if you have this already as a table, you just select the entire table. So in this case, this is
not formatted as a table, so I'm just going to add in more cells. The next criteria is the field. What the field means is the column or the information that
you want to get back. You have different options of
giving the formula the field. You can either type in numbers based on the position of the column, So in our case what
number would division be? Simple, right, it would just be a one. The other option for field is to actually type in the header or
put the header in a cell. And I'll show you that
version in a second, but let's just do the numbering first. The next argument is the criteria. Our criteria is right
here because we want name to be Kim West. Now the way you give it the criteria is that you need to
have the header as well because the formula needs to know which column in the database
this criteria belongs to, and it only knows that if
it sees the header in there and it matches the header with
the header in the database, which means you need to make sure you have the exact same spelling here. That's basically it. Close parentheses and press Enter. Kim West works in the utility division. Now let me show you the
other way of writing this. One is to hard code division in here. The second way is to do a cell reference. I'm going to type in division here, make sure that the spelling is correct and then reference that cell
directly and press Enter. Now you can get more fields back so if we also want it the department here all we have to do is
just make sure we have the correct fixing on the cells so this database shouldn't shift when we pull this formula to the side. So I'm going to fix everything. This one should shift. It should go to department. This one, the criteria,
shouldn't shift either, so I'm just going to press F4 on that. Press Enter. Now I can pull the formula across. So we have this sales
department for Kim West. So here we've seen two
main advantages of DGET. One is, you can use it as a live filter, and two, you can LOOKUP to the left. It has no problem doing a left LOOKUP, which is problematic if
you're using VLOOKUP. Now the downside of DGET is
that you can't really copy this formula down and have it
apply to different criteria, so I can't look for Kim West
and also look for Paul Garza because when I pull this down, I have the name and the
criteria fixed here, I need to have a separate
list for my criteria that's setup like this, so
I need to have the header as well as the criteria I'm looking for. So if you're looking for a formula that you need to pull down
because you want to look for divisions and departments
of different names then you need to use another formula like Index and Match, or if
you're not looking to the left then you can also use VLOOKUP. And of course, in future
Excel, you can use the new XLOOKUP function. Now another advantage of DGET is that it does approximate matches so if I don't know the exact
spelling of Kim's last name I can type this in and it still works. And if I switch this to someone else, so let's go to Daniel Garrett
the filter is dynamic. I get productivity and finance for Daniel. One other thing that
Excel's DGET function can do is also look for OR criteria. This is not going to be
common but it could happen. So let's say that Kim West gets married to Stevie Bridge and she
changes her last name to Kim Bridge. Now you receive a dataset
and you don't know if, in that dataset,
if she's still Kim West or if she's Kim bridge. You can apply the OR version
of the DGET function, and the OR version looks like this. You just type in the second name that could be in this dataset, so I now have Kim West and Kim Bridge. We need to update this formula
to include both of these and let's applied to both here. Now I have Kim West in
there, everything works. If I happen to have Kim Bridge in there it will work as well. But here's the problem with DGET. If you have duplicate criteria, so if your list is not
unique you will get an error. So lets say I had Kim West
and Kim Bridge in there. I'm just going to update this to Kim West. We get this error, and
this error usually means you have duplicate criteria. And it's not just for OR conditions, it's actually for single criteria as well. So if I just reverse
this and put this here, and lets say I had Kim West
two times in this dataset, just copy and paste it
here, I also get this error. So for VLOOKUP or Index and Match you always get back the first match. With DGET you get this error instead. Now let's take a look at using
DGET with multiple criteria. This is a plus for the DGET function because you can lookup multiple values. This is something you can't
easily do with VLOOKUP or with Index and Match. You have to create a unique code or create very complex functions but it's really simple if
you use the DGET formula. So here we have division,
sales manager, region, app and profit, and we want
to get the sales manager and profit for this region and this app. So basically the combination of these two. All we have to do is type
in DGET, our database, remember it includes the headers, so I'm going to highlight my range. Now since I want to
pull the formula across I'm going to fix this reference. Next is the field, my field
is sitting right here, and then I have the criteria
which is sitting right here. Now region and app the
spelling is identical. I just have to include them here. Then press F4 to fix it. Close the parentheses and
that's the sales manager for this combination. So if we scroll down, we see
North America and Blend here. Robert Marquez is the sales manager. Our profit should be 1042. Let's see if we pull this
across if that's what we get. And that's correct. Right, so one thing you
have to be careful though again is that you have to make sure that the combination of region and app is unique in your dataset
otherwise you're going to get the #NUM error. Now here's just to recap
the main advantages of the DGET function is that
it can LOOKUP to the left. It can also LOOKUP text
as well as numbers. It can look for OR conditions
as well as AND conditions. And it acts as a live filter. The main disadvantage of the DGET function is that you can't apply
it to many criteria so which means that you
can't pull down the formula. If you need to do that, you
need to use other functions like Index or Match or VLOOKUP. Th other disadvantage of DGET is that you get the #NUM error if you have duplicates in your dataset. So you don't get the first match back like other Excel functions. So that's how Excel's DGET function works. It's easy to use but it
has its set of limitations especially when it comes to applying the formula to many cells. I hope you enjoyed this video. It was a common request on this channel. Give it a thumbs up if you liked it. And subscribe if you're new
here and you like what you see. And I'm going to see
you in the next video. (upbeat music)
