Video people wanting to understand how that function worked this is that video but first let me introduce you to the simplest version of the lookup function and then we're gonna work our way up.
the lookup function is actually super simple in terms of the arguments that you need for it so here i have a list of employees and their salary i need to.
Figure out the bonus that applies to each employee based on their salary and here's my guidance so if an employee earns between thirty thousand to fifty thousand they should get five percent if.
They earn more than a hundred thousand they should get fifteen percent so we're going to start off with lookup the first argument we need is the lookup value where we're looking for their.
Here and i'm planning to copy my formula down so i'm going to make sure that i fix this using the f4 key so my referencing doesn't shift then i need the result vector and that's right here.
I'm going to use f4 to fix that as well so notice there is no more argument to define if i want an exact match or an approximate match what the lookup function does if it doesn't find an.
Exact match is that it is going to give you an approximate match so here for kim i get 10 is that correct kim earns between 60 to 100 and i get 10 percent that's correct.
Let's just drag this down and see what we get here for n we get 15 that's correct as well now the reason this works correctly is because my salary column is sorted in.
Ascending order if it's not you can get wrong results so basically what the lookup function does is that it expects this to be sorted in ascending order it goes and looks for 89.
Posts Related:
500. if it doesn't find it on this list
It's going to give you the largest number that is smaller than what you're looking for so the largest number here that's smaller than what we're looking.For is 60 000. that's why we end up with a 10 percent and it works because our salary column is sorted in ascending order so now i want you to tell me why this one that i'm gonna write right now.
Is not gonna work okay so you have to pay attention attention this time we need to find the id for each employee we have the name of the employees here and their id is.
Sitting in the k column now the great thing about lookup is because we can specify a separate return range to our lookup range it doesn't matter if that range is on the left or the right side.
Of our table which is great so to get the id i'm going to start off with lookup my lookup value is the name of the employee my lookup vector is right here i'm going to fix it using f4 and my.
Return vector or result vector is the employee id i'm going to fix it with f4 close bracket press enter i get id 16 for kim west that's not correct.
Send this down and just see what we get we get 14 and 16 but they're assigned to the wrong people so there is a problem here can you figure out what that is the problem is that my lookup vector.
Isn't sorted in ascending order so i'm looking up names from this column here i have to make sure that the column i'm looking things up is sorted in ascending order otherwise lookup is going to give.
Me wrong results so i'm just going to select this right mouse click sort sort a to z and now we end up with the correct results let's just double check kim west.
Should be id 13 that's correct karina
Smith should be 16 and that's correct so keep that in mind whenever you use the lookup function your lookup vector needs to be sorted in.Ascending order now let's take a look at an advanced lookup situation so here i have different bom levels and part numbers i want to find the.
Immediate parent part so for example the parent of this one is zero the part number that i should get in front of this in this row is going to be this one right here the part number for these is.
Going to be this one the part number that sits in front of this one is going to be this one here because this is the parent of this one now what's important here is that we always want.
The immediate parent so the parent of these is this is number two but the parent of these is not this number two it's only this one right so we're always looking at the last match.
How could we solve this with our traditional lookup function well let's think this through when i'm at the zero level there should be nothing because there's no parent here when i'm at this.
Level then i need something i need this one so i'm actually gonna start writing the formula here once we've figured out the right function then i'm going to copy it up as well so we're going to.
Start off with lookup the lookup value well we are going to look for the parent of this which is going to be this minus 1. and what about my lookup vector well i just want to look at the.
Immediate parent so i could probably do something like this where i fixed the beginning of my reference and i let the end be variable so as i drag this down my range expands.
Right because i don't want to highlight everything i don't want to find the future part numbers because i need the ones from the immediate parent the one that's right above me.
So i'll go with the lookup vector as this and the result vector well that's going to be the same range and in the same format i'm going to fix the beginning and leave the end variable.
Right so when i close the bracket and i press enter in this case it's correct i've just enabled conditional formatting so that we can easily track if everything is.
Working now when i drag this down this part is correct as well this is correct too but here i end up with problems why is this problem happening.
This is the number i have here for two and what do i need to see i need to see this two and not that two because this is the immediate parent of all of these now the reason this is happening and you.
Might have guessed it is that this part the lookup vector isn't sorted in ascending order and i can't sort this in ascending order because it doesn't make sense in this case a way around this is.
To create our own lookup vector that consists of either zeros or ones or errors or ones because that way we're not going to have that ascending problem let me show you what i mean so instead.
Of looking up for this number here i'm going to bring that as a check we're going to bring it to our lookup vector and create a vector that checks whether this value minus 1 equals our parent let.
Me show you what we get in this case if i highlight this and press f9 we're going to get true and false so it means that the first one is the parent the second one isn't which is great but now.
How do we use this as a proper vector we could convert our false values to errors and our true values to ones and remember in excel false and trues can be easily converted to numbers by doing a.
Mathematical operation on these and i could do one divided by this here now check this out when i highlight this and press f9 we get one and error if my lookup vector only consists of ones and.
Errors this ascending problem is not a problem anymore all i need to do for my lookup value is to look for a number that's greater than 1 and it can be any number as long as it's greater than 1.
Because what's going to happen is this everything is going to work properly now we can just double check here just to make sure that we understand how this.
Part works if i'm on this cell down here i'm going to press f9 so you can see the result that we get we end up with errors and a bunch of ones right because whenever we.
Get a 2 we get a match now because the lookup function returns the largest number that's smaller than our lookup value it's going to end up giving us the last.
Match which is exactly what we need to solve this problem now let's also account for whenever we have 0 in this case we are going to end up with an error so i'm just going to use the.
Iferror function in case our result is an error we're just going to return a dash now let's just send this down so we are consistent.
Now with the introduction of the xlookup function in office 365 and office 2021 this type of lookups have become so much easier to do because you don't have to use these type of tricks so i'm going to.
Go to this cell just like we did before and start with my lookup function the lookup value is more logical to set up just like you would the way you would think this through we're gonna go with.
This minus one this is going to give us the node for the parent then the lookup array just gonna select what we had before we're gonna expand this i'm going to fix the beginning and keep the end.