2

VLookup with multiple values

So, you can use a VLookUp to retrieve a piece of information.  You can use SumIf to total a range based on criteria and you can use CountIf to count how many times criteria appears.

What do you do though when you have to create a VLookUp with multiple values?

The VLookUp will run through your array and when it finds your lookup value it retrieves the relevant data and then stops searching.

If like my example below you have multiple occurrences of your lookup value, how do you retrieve all order numbers for Alan?

As you can see the VLookUp only shows the first Order number for Alan.

 

The full formula is {=IFERROR(INDEX($B$3:$B$12,SMALL(IF($D$3=$A$3:$A$12,ROW($A$3:$A$12)-ROW($A$3)+1),ROW(2:2))),””)} where the value we want to check is in D3, our table is A3 to B12.

You do not type in the curly brackets ( {} ).  Enter the rest of the formula and then press Ctrl+Shift+Return/Enter which changes the formula to an array formula.

The formula may look complicated but I break down what makes it up below, it is simpler than it may look but you will need to know some basic-intermediate Excel to work through this formula.

VLookUp With Multiple Values

In the past I have suggested the user tries PivotTables or macros (depending on their ability and knowledge).

The last time I was asked though, neither of these options were ideal and a VLookUp with multiple values formula would have been preferable.

Does this formula exist?

If it does I haven’t come across it yet.  So what is this post about?

I can’t create a VLookUp with multiple values formula, but what I can do is show you how to take an IF formula, combine it with other formulas and hey presto you have a list of all order numbers for Alan.

1. VLookUp With Multiple Values: IF

The basis of our full formula is going to be an IF statement.

In column F3 our formula will be =IF($D$3=A3,B3).

We will compare the name in D3 (Alan) against the value in A3, if they are the same, then the result of our formula should be the value in B3.  If they are not the same then our formula will result in “FALSE”.

If we copy this formula down for the same number of rows as we have in our table you will see the order number whenever it finds “Alan” for “FALSE” if it doesn’t find Alan.

We made D3 an absolute reference ($D$3) so that when we copy it down rows it stays at D3 and does not change to D4, D5, etc.

We could add in a blank (“”) as the value_if_false syntax.  This would mean that instead of seeing “FALSE” in our results, the cell would be blank.

This still doesn’t help us, but we are getting there.

 

2. VLookUp With Multiple Values: Array

An array for our purposes is basically a collection of items.

We will use the array to calculate multiple calculations.

In the next section, we will add ROW to our IF formula and then make this formula an array formula.

For now, the basic way to think of an array formula would be to use again our example above.

Instead of having a formula that compares D3 to A3, then a formula that compares D3 to A4, then D3 to A5, etc. we can create a formula that compares D3 to the range A3 to A12 at once.

Our array or collection will be the values of A3, A4, A5, etc. and each will be compared to D3.

 

3. VLookUp With Multiple Values: ROW

The ROW formula returns the row number of a reference.

The ROW of cell A3 is 3, the ROW of A12 is 12 and so on.

In column G3 I have entered the formula =ROW(B3).

When you copy this down the row number changes.

In our IF statement, our value_if_true was B3, then B4, B5 and so on.

If we used a HLookUp we would enter a row index number.

When we combine the IF statement with ARRAY we want to get the ROW number every time that the formula finds “Alan”.

Follow the steps below and bear with me…

1. In H3 enter the formula =IF($D$3=$A$3:$A$12,ROW($A$3:$a$12)).
2. Copy this formula down to H12
3. With H3 to H12 highlighted, press F2 to enter edit mode
4. To change these formulas to arrays, press Ctrl+Shift+Return

You should notice that the formula is now contained within curly brackets ( { } ).  You do not type these in.

Want to see what makes up the arrays?  Highlight the cell ranges within the formula and press F9, instead of seeing the range you see the values of the range.  When you press enter or click away you will get a message saying you cannot change part of an array, this is OK.

 

Still with me?  I hope so because now we are going to make a slight change to this formula.

As you can see, the formula gives us the row numbers 3, 6, 9 and 12.  These are the row numbers of the sheet, where Alan’s name appears.

However, later we want to know what row of our table Alan’s name appears in.

If we take our table to be A3 to A12, then A3 is actually row 1.

If your table starts on row 1 and you include the headers in your array range, then you don’t have to do this.

In the real world, I come across a lot of cases where tables do not begin on row 1 so I will include the adjustment here.

If we take A3, on the sheet this is row 3, in our table this is row 1.

We will deduct the row number of A3 from array’s row number and add 1.

So, in the first row this will be A3 – A3 + 1 (3-3+1=1), then A4-A3+1(4-3+1=2).  So A3 is row 1 of our table, A4 is row 2, etc.

Now we can see that Alan is found in rows 1, 4, 7 and 10 of our table from A3 to B12.

 

4. VLookUp With Multiple Values: SMALL

The SMALL formula returns the k-the smallest value in a data set.

We are going to use it to get the smallest value in our existing formula (1), then our second smallest (4), and so on.  This will give use the row numbers where Alan is found in our table in order.

1. Recreate the last formula you created.
2. After “=” but before “IF” type “SMALL(“.  This starts the SMALL formula, our array will be the formula we have created so far.
3. At the end of the formula, type “,ROW(1:1))“. This creates our k in the k-th value.  It starts at 1 for the smallest value, then goes to 2 for the second smallest, and so on.

Now we have the smallest row number, second smallest number and so on until it cannot find a row number and just gives #NUM!.

We are over 1,000 words into this post and you are still reading this, you will be glad to know it is almost over.

 

5. VLookUp With Multiple Values: INDEX

The INDEX formula returns the value of a cell at the intersection of a row and column.

Our column will be column B.

Our row will be the value of our formula so far.

1. Recreate the formula as before.
2. This time after “=” and before “SMALL” type “INDEX($B$3:$B$12,” and add a closing bracket at the end.

Our column is B and the row is the value of our formula from the previous formula.  This now gives us the value of the cells at that intersection point.

To finish this, add an IFERROR that replaces the error message with blanks.

 

Note!!

When you copy this formula down rows, I would advise that you copy down as many rows as there is in your table.  This ensures that you catch all possible instances of your lookup value appearing.

In my case above if I copied the formula down only twice, then I would only see 3 order numbers for Alan, even though there are 4 in the table.

 

[grwebform url=”http://app.getresponse.com/view_webform.js?u=CucD&wid=4307503″ css=”on” center=”off” center_margin=”200″/]