Excel Tip - Use VLOOKUP to Return Multiple Values Horizontally

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.

BJ Johnston has been an advanced Excel user for 15 years and is the creator of http://www.howtoexcelatexcel.com a site that shares Excel tips and tricks with it's enthusiastic members. To join in the discussion, where users are working smarter and faster with Excel sign up for the FREE newsletter and as a bonus receive a FREE EBook- 50 Top Tips and Tricks.

Article Source: http://EzineArticles.com/expert/BJ_Johnston/850729

Article Source: http://EzineArticles.com/9075152