Excel Tip - Use VLOOKUP to Return A number of Values Horizontally

 I've been requested this questions quite a few time about Excel and VLOOKUP formulation. The best way to return a number of values from one VLOOKUP.

For instance we've prospects who've multiple piece of kit, however we wish to return the serial numbers of all these items of kit utilizing the VLOOKUP method. Usually prospects have one merchandise that we wish to match, so a standard VLOOKUP matches our wants however some have multiple. We have to do a bit extra with our VLOOKUP and switch up the volumes on this method.

So, let's do that with some knowledge... here's a listing of shoppers and their gear (a really small extract of our knowledge set).

Buyer Quantity Tools Sort Serial Quantity

776546 Baffle 1432

776546 Stoker 1552

776546 Plank 1749

776324 Plank 1500

764632 Baffle 1683

763452 Baffle 1815

763543 Stoker 1263

OK, so trying up the a number of varieties or serial numbers is very easy on this instance with such a small knowledge set, however what about if we've hundreds of data, that may be fairly powerful. Let's get Excel to do the powerful work, and write a method for get this job completed.

{=INDEX($A$2:$C$8,SMALL(IF($A$2:$A$8=$E$2,ROW($A$2:$A$8)-1),COLUMNS($F2:F2)),3)}

Firstly that is an array method. An Array method is a method that may carry out a number of calculations on a number of units of values). The INDEX perform seems in our desk ($A$2:$C$8)

• It then makes use of the SMALL perform to search out which row to look in after which subtracts 1 from what SMALL tells us. ( it takes the smallest row quantity first and matches the worth in Column C)
• The ROW perform returns the row within the worksheet the place our price was discovered.
• The INDEX perform considers the primary row of our desk as row 1. As a result of our desk knowledge begins in row 2, we have to subtract 1 from the ROW worth to get the proper row contained in the desk.
• In your individual spreadsheet you could want to regulate this worth to take account of the place your knowledge desk is positioned
• The INDEX perform then seems in column 3, which is the place the information is discovered.
• As we drag the method alongside the columns, the subsequent occasion of the row quantity I.e 2 in F3 and three in G3 and so forth till there are not any extra matches and we get an error. Job completed.