Vlookup allows you to search data for a value and return a corresponding value from the sale row. Vlookup is great for returning values like a price for a particular item or stock on hand for a particular item being sold. Vlookup however has one limitation because Vlookup only returns one value at a time. To use Vlookup for returning multiple values, you need to either use multiple instances of the Vlookup formula or you need to use an array formula. This tutorial will show you how to use the array formula to lookup multiple values within a table where values are repeated within the table.
This tutorial requires a good knowledge of the use of Excel. To improve your Excel skills, Excel 2013 For Dummies Video Training, Deluxe Edition will take your Excel skills to the next level. This course offers over 106 lessons and 19 hours of video content designed to turn an Excel beginner into an Excel Professional. This course will teach you all the basic skills required to use Excel 2013, from launching the application to navigating your workbooks. You will learn to enter data and how to use the AutoFill feature to allow for simpler data entry. You will then learn to add formulas to your worksheets. Once you have mastered the basics, you will move onto pivot tables and charts and other advanced skills. The course will even teach you to share your workbooks via SkyDrive.
Using an Array Formula to Look Up Multiple Values
This tutorial will use the following data for the tutorial:
If we wanted to return Inventory for an item, we would use the following formula:
And the answer would look like this:
The problem with the formula is that there is more than one instance of “Pen”. In this case we need to use an array formula to look up each instance of pen and return the value that corresponds to that instance.
The Array Formula for Looking Up Multiple Values
Take a look at the following formula. This is the formula we will need to use to return multiple values using lookup:
It looks a little intimidating but we will break down the formula so that you can see what is going on the formula itself.
The If and the Row Function
Let’s take a look at the center portion of the formula:
The IF function can be used to compare two values to see if they are the same. In Excel, if we say “IF cell A1 equals ten,” then Excel will check the value of cell A1 and if the value is ten, then the function will have a result of true and Excel will do something and if it is false, Excel will do something else. The row function returns the row number of a reference. So in the formula above, “IF” tells Excel to compare the values of two cells. In this case we enter a value into cell A10. So Excel checks if the value of cell A10 is equal to the values in the range from A1:A7. We use absolute references because we will be copying this formula to the next few rows therefore the formula is $A$1:$A$7. If the value in the range is equal to the value we entered into A10, then Excel will return the row reference of the cell.
The Small Function
Let’s take a look at the next part of the function:
The SMALL function returns the smallest value in the data set for the corresponding value. Take a look at our spreadsheet below to understand what the “SMALL” function returns:
Small therefore returns the row number of the rows where the Items equal A9 which is the value of “Pen”. In other words, Pen appears in row 2 and row 4.
So now we have a way to return the row numbers, but what we are really after is the value that occurs in those row numbers. This is why we use the INDEX formula.
For a comprehensive Excel course, the Learn Microsoft Excel 2013 offers over 102 lessons and 3.5 hours of video content to make sure you know how to use the features and functions of Excel 2013.
The INDEX Formula
The Index formula returns the reference to a value of an element in an array by selecting the row and column.
Take a look at a simple example of the INDEX formula:
In the formula above, the result would be paper since Excel would search the array A1:B7 and return the value it finds in row number 3 column number 1:
Therefore it would return “Paper” based on the above table.
So let’s take a look at our complex formula again:
In this case the array table is still A1:B7, the row number is calculated using the “SMALL” formula to return the smallest value within the data set, “IF” the value of the data in the table is equal to the value we entered in A10.
This formula is a very complex formula, but when you break it down into its parts, you can see how the formula provides us with the results we need.
It is important to note that the above formulas are based on array values. You therefore need to enter the formula in the cell and then press CTRL + SHIFT + ENTER to enter these formulas into the cells. If you do not CTRL + SHIFT + ENTER, then the above formula will not work.
For more advanced courses in Excel, enroll in the Microsoft Excel 2013 Beginners/Intermediate Training course and join over two thousand students learning to harness the power of Excel. This course offers over 58 lessons and 14 hours of content designed to take you from beginner to advanced Excel user. It contains information on a number of advanced formulas including some of the new Excel formulas. You will learn to work with Pivot tables, pivot charts and other advanced Excel features and functions.