To work for us we have our sales data here this is the customer number we need to figure out the customer locations and then give that list to the original manager.
Problem is the customer location is sitting somewhere else it's not here on each row it's sitting in this file called customer i have customer number name last name city and location is.
Right here that's what i want okay so let's see if i remember vlookup correctly i'm just going to collapse this i've learned that from the pro before.
We're going to start off with vlookup the lookup value is right here the lookup table or table array is called is sitting on our other file.
Now i have to mention the column that i want to get back where is that one two three four five it's the fifth one that's it close bracket press enter.
We have it we have oregon now let's just double check i'm gonna copy this always double check your data and your results to make sure it works.
This one and we have illinois it's not working why look i have a problem i did what you.
Said but vlookup is giving me wrong results it tells me this customer is from oregon but they're not they're from illinois let me see your formula.
Just by looking at this i can see the problem when you need the exact match for your lookup value you have to put false or zero as the last argument see now it's correct.
Posts Related:
I just copy this down and finish the
Report okay okay don't make me nervous i'll drag this down actually i'm just going to double click and send this down now i get the locations multiple times.But it's not a problem i know how to remove this i'm just going to highlight this column where was that remove duplicates i think it was in the data tab.
Let's just double click it's right here remove duplicates expand the selection no we're going to continue with the current selection remove duplicates location okay.
Seven unique values remain wait a second what happened to my formulas oh shoot i shouldn't do it here thankfully we have ctrl z to go back i'm gonna copy.
And paste as values and then this data set i'm gonna remove duplicates and that's my list now that i can send to the regional manager.
One shift later i need your help again now he wants to report by product name too i try to do it with vlookup but it's not cooperating that can't be let me see.
Okay look let me explain i'm just gonna do it from scratch so you can follow along here i have the product number and the product names are again in a separate.
File so i did exactly like we did before i went with vlookup the lookup value this time is the product number and the other file is right here that's my list of products this is the product name.
That's what i want and that's the product code i don't care about these other stuff so i only want these two my lookup value is this one right the number and this is.
What i want returned
So i went with the column index number i went with minus one and for the range lookup i went with false close bracket press.Enter and i get this error okay look vlookup can't look to the left it can only look to the right of your lookup value you probably don't want to change.
The order of the columns in this master file you could use index match but let's stick to something simple since you have office 365 use x lookup just like i told you from.
The start here watch this video it explains everything in detail you should also subscribe to the channel she has a lot of excel videos you can learn from.
Interesting i'll watch it a few moments later okay so let's try this out with x lookup we're going to start off with x lookup lookup value is our product number.
The lookup array so it's different to the return array it's only the range that this number is sitting on in my products file which is here.
Now i need the return array which is this one that's why it doesn't matter whether i'm looking to the left or to the right x lookup is great default is exact match so i don't need.
Anything i am just going to close the bracket where's my bracket it's here press enter and i have it let's just double check that this is correct memory foam seat.
Cushion that's memory foam seat cushion so that looks good it works now let's send this down.
Hmm what is this look i did it i have product name in here the only problem is i get these hash n a stuff and they're so ugly can i just.
Remove them can i delete the formula no please don't an a means that x lookup couldn't find a value for your product code it's probably missing from your master data file you can use the if not.
Found argument to make this go away we can say update master data or whatever else you want here then just copy the formula down oh my god you're so grumpy but you saved my day thank you have a.