Return multiple values from a ‘Lookup’ using an Array Formula (Advanced)

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

A common requirement in Excel is to view particular subsets of some data, perhaps on a separate sheet. In the Topic Example Workbook, we have all of our sales data on one tab and then on a separate sheet (or sheets) want to see some of this data – that related to a specific salesperson.

Even without using macros, there are a number of ways to do this. For example we could filter the data for the salesperson we require and then copy and paste it into the sheet(s) we want, we could create Custom views of the data with relevant salespeople filters applied, or we could create a pivot table by salesperson and then drill-down into the people that we want to see the sales data for.

However all of these methods are somewhat manual and don’t allow us to build a truly dynamic data analysis model like we have in the Topic Example Workbook: there we have a drop-down of salespeople on the ‘Analysis By Salesperson’ tab and then by selecting a salesperson we want to see below all of the sales data relating to them. Essentially we want to look up the salesperson selected and return all the values (in columns we specify) where that salesperson value occurs in Column A of our sales data – i.e. do a lookup but return multiple values.

In this topic we’ll look at a way of achieving this through using array formulas. Before continuing it is important to have an understanding of how array formulas work which you can obtain by reading the topic Creating your own MAXIF/MINIF formula using an array formula

In the second sheet of the Topic Example Workbook, the completed lookup array formula we require in Column A (which returns an array of the row numbers) is:

{=IFERROR(SMALL(IF([Lookup Range=$B$1,ROW(Lookup Range)),Row(1:1)),0)}.

This formula returns the row numbers in the ’Sales Data’ tab where the Salesperson in Column A is the one selected. So if we have Andrew selected as the Salesperson and this formula in cells E5:E12, we will return 2 (in Cell E5) and 10 (in cell E6) because there are two occurrences of ‘Andrew’ in Column A of the Sales Data tab – one in row 2 and one in row 10. The rest of the cells (E7:E12) will just be blank. If we change the salesperson to Richard we then want to return 4 in Cell E5 and 9 in cell E6 because these are the rows numbers where Richard occurs in column A of the Sales data. Once we have the row numbers, we can then use an INDEX function to return the corresponding values from Sales data for those rows – i.e. the relevant data for that salesperson.

Below are the steps taken to build up this formula:

• Write an array formula to return the list of row numbers where the salesperson (stated in cell $B$1) occurs in the sales data worksheet (‘Sales Data’!$A$2:$A$12):

  ={ IF(‘Sales Data’!$A$2:$A$12=$B$1,ROW(‘Sales Data’!$A$2:$A$12)) }

• This returns an array of row numbers (e.g. {2,10} ) for salesperson Andrew.
• Isolate the first value in this array (i.e. the first row number which contains sales data relating to Andrew) by using the SMALL function: =SMALL(array,k) This function takes an array or range and returns the kth smallest value in that array/range. So referring to our array of row numbers:

  =SMALL(IF(‘Sales Data’!$A$2:$A$12=$B$1,ROW(‘Sales Data’!$A$2:$A$12)) ,1) returns the 1st (lowest) value in our array of row numbers – i.e. 2

  =SMALL(IF(‘Sales Data’!$A$2:$A$12=$B$1,ROW(‘Sales Data’!$A$2:$A$12)) ,2) returns the 2nd lowest value in our array of row numbers – i.e. 10.

  =SMALL(IF(‘Sales Data’!$A$2:$A$12=$B$1,ROW(‘Sales Data’!$A$2:$A$12)) ,3) would return the 3rd lowest value in our array, but since there are only two values this returns a #NUM error.

• Replace the numbers at the end (1, 2 etc) with Row(1:1), Row(2:2) so that the formula extends as we drag it down. This makes our formula in cell E5

  {=SMALL(IF(‘Sales Data’!$A$2:$A$12=$B$1,ROW(‘Sales Data’!$A$2:$A$12)), ROW(1:1))}

• Error trap the #NUM errors (where we are looking for the kth smallest value beyond the number of values in our array) using =IFERROR(Formula,””):

  {=IFERROR(SMALL(IF(‘Sales Data’!$A$2:$A$12=$B$1,ROW(‘Sales Data’!$A$2:$A$12)),ROW(2:2)),””)}

Populating this formula in cell E5 and dragging down to E12 returns 2 in E5, 10 in E6 and blanks in rows E7:E12. We can then use these row numbers as part of an INDEX function to extract the data in the relevant columns:

E.g. to return the corresponding product sold, the formula in cell F5 in our Analysis by Salesperson tab would be =IF(E5=””,””,INDEX(‘Sales Data’!B:B,E5,1)).

This states that if E5 is blank then return nothing, otherwise returns the value from Column B of the Sales Data tab (Product Sold) where the column no is 1 and the row number is that which we identified from our rows array formula.

The same thing can repeated for the other columns to bring in the relevant data required.

The second sheet of the Topic Example Workbook (Analysis By Salesperson) has a drop-down of Salespeople in Cell B1 and then underneath has columns to build up an array formula for eventually returning the Product/Quantity sold by that Salesperson (in Columns F and G).

The different columns for building up the formula relate to the steps above (e.g. Column A and B is the first step of returning the row numbers, column C combines this with SMALL to return the first instance and so on). The column Headings (in Row 4) show the syntax of the formula required in each column.

Go through each of these columns and build up the array formula. Resist the temptation to look at the answers before trying to write the formula yourself!

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

• Be careful of how far you drag your array formulas down in the analysis tab – not far enough and you won’t pick up all the relevant rows, too far and your workbook will become unnecessarily very slow to work with.
• For quite large datasets, it is better and quicker to use macros or one of the manual methods described above to see data sub-sets
• You can bring out a subset based on more than one lookup value by adding additional IF statements before returning the row number. E.g. to filter for where the value in ‘Sales Data’!$A$2:$A$12 is the Salesperson in Cell $B$1 and where the value in ‘Sales Data’!$B$2:$B$12 is equal to “CD” our lookup array formula becomes:
• =IFERROR(SMALL(IF(‘Sales Data’!$A$2:$A$12=$B$1,IF(‘Sales Data’!$B$2:$B$12=”CD”, ROW(‘Sales Data’!$A$2:$A$12))),ROW(1:1)),””)
• Obviously with lots of conditions this array formula gets very messy and you may then be better off considering alternatives!

If

• the array formula cell is edited and then not re-entered using Ctrl + Shift + Enter then it will fail to operate. If you want to prevent this (or if you just want to hide the array formula from the sheet) you can have it as a [named formula] – Go to Formulas -> Name Manager -> New, give it a name (e.g. ‘GetSalespersonRows’) and then in the ‘Refers to’ box enter the array formula without the curly brackets. Then in the sheet you can just enter your name e.g. =GetSalespersonRows.