I have been asked this questions a number of time about Excel and VLOOKUP formulas. How to return multiple values from one VLOOKUP.
Let’s say we have customers who have more than one piece of equipment, but we want to return the serial numbers of all those pieces of equipment using the VLOOKUP formula. Normally customers have one item that we want to match, so a normal VLOOKUP fits our needs but some have more than one. We need to do a bit more with our VLOOKUP and turn up the volumes on this formula.
So, let’s do this with some data… here is a list of customers and their equipment (a very small extract of our data set).
Customer Number Equipment Type Serial Number
776546 Baffle 1432
776546 Stoker 1552
776546 Plank 1749
776324 Plank 1500
764632 Baffle 1683
763452 Baffle 1815
763543 Stoker 1263
OK, so looking up the multiple types or serial numbers is really easy in this example with such a small data set, but what about if we have thousands of records, that would be pretty tough. Let’s get Excel to do the tough work, and write a formula for get this job done.
{=INDEX($A$2:$C$8,SMALL(IF($A$2:$A$8=$E$2,ROW($A$2:$A$8)-1),COLUMNS($F2:F2)),3)}
Firstly this is an array formula. An Array formula is a formula that will perform multiple calculations on one or more sets of values). The INDEX function looks in our table ($A$2:$C$8)
- It then uses the SMALL function to find which row to look in and then subtracts 1 from what SMALL tells us. ( it takes the smallest row number first and matches the value in Column C)
- The ROW function returns the row in the worksheet where our value was found.
- The INDEX function considers the first row of our table as row 1. Because our table data starts in row 2, we need to subtract 1 from the ROW value to get the correct row inside the table.
- In your own spreadsheet you may need to adjust this value to take account of where your data table is located
- The INDEX function then looks in column 3, which is where the data is found.
- As we drag the formula along the columns, the next instance of the row number I.e 2 in F3 and 3 in G3 and so on until there are no more matches and we get an error. Job done.