VLOOKUP and Array Formulas are Changing The GameMatthew JohnsonBlockedUnblockFollowFollowingMay 14Microsoft Excel is one of the most widely used tools in the modern workplace, but it is also one of the most widely underutilized.
I think that should change and I think it should start with VLOOKUP.
That’s why, I’d like to take a moment to talk about using VLOOKUP as an array formula.
What is an array formula?An array formula is a feature of Excel that allows you to perform a formula operation on more than one value.
What the heck does that even mean?Well, let’s say that I have two data sets that I’d like to join togetherAll Business Licenses in the City of ChicagoAll Business Owners in the City of ChicagoThe Business License file has all the data I need about the license itself, but the individual owner’s name is located in the Business Owners file.
I don’t want to flip from sheet to sheet to see the owner’s name, so I want to pull the owner’s first name and last name into the Business License dataset.
To do this, I will use an VLOOKUP array formula to pull in two values instead of one.
Using VLOOKUPIn order to join the two files together, I need to find a common element between both files.
Luckily we can see that in both the License and Owner files, we have an account number that we can use.
The account number is a common field between both filesSetupInside the License file, I will create two new columns to the right of the Account Number field.
These new columns will hold my first and last name fields.
In an Excel file, you can create a new column by right clicking and selecting insert, or by simply hitting ALT+I, CJoining the dataNow that I have my sheets, set up, I am going to use the VLOOKUP as an array formula to pull in the first and last name data from the Owners file.
I have both files open and side by side for easier reference.
First, I select cells D2 and E2 in the License file, as that where my first row of data will be going.
Select the cells that you will be populatingNext, I will start typing my VLOOKUP formula.
VLOOKUP takes a few input values1.
Lookup Value2.
Table Array3.
Column Index Number4.
Range LookupIn this case, my lookup value is going to be the Account Number from the licenses file, or C2.
My Table Array is going to be the data in the owners file, starting with the Account Number column and going to the Owner Last Name column.
My column index number will be First Name and Last Name, or columns 3 and 5.
VLOOKUP normally only takes one value, but since we are using an array formula, we can give it multiple values by putting them in brackets — {3,5}.
So long as we have two fields for the data to populate into (D2 and E2), the formula will return the values correctly.
In this instance, it will return First Name to D2 and Last name to E2.
Because this is an array formula, instead of hitting enter at the end of the formula, I need to press CTRL+SHIFT+ENTER.
This will surround my entire formula with brackets and let Excel know that this is an array formula.
Finishing upAfter doing this I can apply the formula to the rest of the spreadsheet.
Finally, I copy everything and Paste As Values so that the data is fully copied to the Licenses file.
ConclusionMicrosoft Excel is a versatile tool that can help you do quick data operations when you don’t quite need a full relational database setup.
What problems are you solving with Excel?.Where does Excel fall short?.I’d love to continue the conversation.
Please hit me up on Twitter with any feedback!.