I often received many requests from my student asking me “Anne, how can I return multiple answer by using one identifier?”. This usually happened when there are trying to look for the activity of a particular identifier (ID) and in fact I usually will came out with the solution like you can do it using pivot table, but some people will prefer to get it done by using Excel formula. Well, I always like to provide’em with different options, so they get to try around to see which work best for them. I believe different people has different way of them working with Excel, nothing’s wrong or right about whichever way, it totally depends on individual preference.
Return multiple output using Excel Formula
In order to achieve as stated, you will be needing helps from a few Excel functions. There is no such straight forward solution to that. In the end, by having all these functions being joint together will then formed a nested function. Not just nested function, but a nested array function. The functions are IF(), ROWS(), SMALL(), IFERROR(), INDEX() and MATCH(). It’s cray cray… I know. So, if you’re still interested, continue reading.
Let me explain to you the example I have as below.
The first range contains all the purchase activity from our customer. In each line, it stated which customer (by using customer ID) and when did they make the purchase plus how much they spend on that particular purchased.
So right now, I would like to list down the activity of the customer by using their ID.
Once they CustomerID is inserted into the cell, the transaction will be listed down at the right bottom section of the screenshot as shown above. Which means the formula will be inserted at the area where the output is going to be. Now let’s get going.
Functions and its usage
In this section of the article I’m going to cover the functions being used in this exercise and also when where you’re going to need them. Of course these functions has many different usage based of different context. I won’t be covering it all in this series. Simply just to give you a rough idea of those functions.
ROWS()
| Syntax | ROWS(Array) |
| Summary | Returns the count of rows of the given reference |
| How to | ROWS(A3:A9) => 7 (7 cells within the reference) |
Example
ROW()
| Syntax | ROW([Reference]) |
| Summary | Returns the row number of a reference |
| How to | ROWS(A4) => 4 (the reference it located at the 4th row of the sheet) |
Example
IF()
| Syntax | IF(Logical_Test, Value_If_True, Value_If_False) |
| Summary | Return TRUE value or FALSE value from a logical test. Logical Test is when any test conduct using any comparison operators. When the test returned a TRUE/False result, user get to specify what happen or what to return as the output when it is TRUE/FALSE |
| How to | =IF(A3>35,” Expensive”, “Affordable”) |
Example
SMALL()
| Syntax | SMALL(Array, K) |
| Summary | Return the smallest number by its rank |
| How to | =SMALL(A3:A9,2) => return the 2nd smallest number within the array |
Example
IFERROR()
| Syntax | =IFERROR(Value, If_Error_Value) |
| Summary | This function specifically handles all sort of error possibility. The example below shows the % difference from last year. #DIV/0! will be returned when This Year value is 0 or no value |
| How to | =IF(A3/B3,”No Value”) |
Example
MATCH()
| Syntax | =MATCH(Lookup_Value,Array, [Match_Type]) |
| Summary | Match function is used to locate the position of a lookup value within a row, a column or a table. The position returned in a formed of index. |
| How to | =MATCH(2797,A3:A9,0) |
Example
INDEX()
| Syntax | =INDEX(Array, Row_Num, [Column_Num]) |
| Summary | Index function will return a value from a column, row or a table based on the coordinate or position index. |
| How to | =INDEX(A3,A9,6,0) |
Example
Step 1: Convert range into a Table object
As we know the transaction of the purchase will grow and expand as soon there are more activity from the customers. Which also means, as soon there are more purchases, the line of the transaction will grow. Converting it into a table is to ensure whenever the activity is added to the range, the Table will expand. And as the Table has expanded, the formula will automatically lookup from the latest size of the table. This is to also make sure the search is always keeping up with the latest transaction.
To convert to table, you can locate the “Insert Table” command from either Home Tab, Insert Tab or using a keystroke. But before that, you must first locate your cursor in within the region of the transaction.
3 different methods of creating Table Object
Home Tab
Insert Tab
*You must be curious why does the name of the command is different, GOD knows why. Neither do I have any idea, but anyways…yea. That’s the button that allows you to convert te range into a Table object.
Ctrl + Tt
To convert the range into a table object, you must make sure your cursor is being in one of the cell of the range, and then apply your keystroke (Ctrl + T) Either of the option you took from above, you will immediate prompted the dialog box “create table” after you trigger the command. Before selecting OK, check the range and the header checkbox. If everything is good, proceed with OK.
Step 2: Renaming Table object
Immediately change the name of the table from the default name like “Table#” into something more relevant and mean have changed the table name from “Table1” to “paid”.
Step 3: Begins with the extraction
Follow the tutorial below to extract the formula. I will be doing the explanation later on.
