You need to select the shared column in both tables. That is why the data needs to be set up as tables. The dropdowns on the right list all the column names in the table selected. The dropdowns on the left allow you to select a table name. In the Data ribbon tab you'll find under the Data Tools section the Relationships icon:Ĭlicking on "New" will allow you to define a new relationship: Of course they are related! That's why my VLOOKUP works.Įxactly! You just have to make the relationship explicit in Excel, and this is how. The first (and almost only) thing you need to do is tell Excel that the "Name" column in the Sales table is related to the "Name" column in the Region table. You have your two data tables, one with the Sales figures and one with the Regions. I would suggest creating some relationships in your data. OK Mister I-Know-Everything-Better-Anyway, what do you suggest? Well that would be a piece of cake, I just would need 250.000 VLOOKUP's.Ģ50.000 VLOOKUP's? Well that would work, but maybe the performance wouldn't be acceptable.
I never have to process those amounts of data. What would you do if you had 50.000 rows each of them containing 5 columns where a VLOOKUP is needed? I would then be able to draw a Pivot Table and I have the info I need in no time.īrilliant! What you did is perfectly correct and will produce the required result. If I want to know how much we sold per region, I first need to retrieve the region info from the "Region Table" into my "Sales Table" using a VLOOKUP, as you can see in cell E5. So, in the above figure, I've got 2 tables, one with sales data per Account Manager and one with the region each Account Manager deserves. And you do that basing yourself on a common column between those two tables, right? That being said, let me explain briefly the content of this lesson: when using VLOOKUP, you basically retrieve information from a certain data table into the table where you need that info. This Microsoft Office Support page provides some useful information about it. It's everything but a complicated feature and is a really handy instrument. Let me first notify our readers of the fact that they, prior to reading this lesson, must be familiar with Excel tables.
I however would like to briefly introduce a nice Excel 2013's feature that will reduce the need to use VLOOKUP. It's almost the only one I master and I really understand. No please don't you take away my VLOOKUP formula.
0 kommentar(er)
