Jul 29, 2014
function, like every other Excel function, returns only one value from a table
. It looks up a table to find a row that has a key value in its first field and then returns a value from another field of that row.
For example, if you are looking up Ashley’s sales amount from the table below, your formula will look like: =VLOOKUP (“Ashley”, $E$4:$I$26, 5, False)
This formula gives you the value of $28.72, because it looks in the first column of the range $E$4:$I$26 to find the first occurrence of Ashley which happens to be in row 4. It then returns the 5th
field of that row which is the “Dollars” field and so it returns the value $28.72.
Now if you haven’t noticed, Ashley also appears in rows 12, 14 and 15, but the VLOOKUP only returns the first instance of Ashley.
What we want to achieve is that when we write the name Ashley, it will grab all dollar values of her sales from the table, something like the one shown in Figure 2.
There are many ways to achieve this, mostly by using Array Formulas
, however we are going to stay away from them and use ordinary, familiar ones instead.
What we need to do is use VLOOKUP in multiple cells
underneath Ashley. Each of these VLOOKUPs looks up a range that does not include the row that has been found in the previous VLOOKUP. In other words, each VLOOKUP includes a range that starts from the next row found by the previous VLOOKUP (the VLOOKUP above it). As an example, the VLOOKUPin cell L4 in Figure 2 looks up the entire range $E$4:$I$26 and finds row 6 and returns the value in I6 and that’s Ashley’s dollars. VLOOKUP in cell L5 then looks up the range $E$7:$I$26 because row 6 contained the previously found value so it should start searching from row 7 hence $E$7:$I$26.
Since this is a sequential calculation, we need to store the row number of the previously found value somewhere. I decide to store them in column K next to Ashley’s dollar values. Therefore, the results will look like those in Figure 3 below.
As you can see, column K now contains row numbers at which the name Ashley occurs except of course, the first one which is 3. This has been entered manually and it indicates the starting row of our table.
The rest of the figures are calculated automatically. So let’s see what the formula looks like.
Cell K4 has this formula: =ROW(INDEX(INDIRECT(“$E$”& K3+1 & “:$i$26”),MATCH(L$3,INDIRECT(“$E$”& K3+1 & “:$E$26”),0),1))
Explanation: let’s put the value of K3 which is 3 into the formula;
=ROW(INDEX(INDIRECT(“$E$”& 4 & “:$i$26”),MATCH(L$3,INDIRECT(“$E$”& 4 & “:$E$26”),0),1))
The INDIRECT() function converts a text into a cell reference therefore instead of
INDIRECT(“$E$”& 4 & “:$i$26”), we’ll have $E$4:$i$26
MATCH(L$3,$E$4:$E$26,0) returns the first occurrence of Ashley (L$3) in the first column E and INDEX return its cell reference which is E6. ROW therefore returns the row number of that cell which is 6.
By dragging the cell down, the formula increments K3+1 to K4+1, K5+1, etc. and therefore, subsequent row numbers that match Ashley will be automatically calculated.
Now it’s time to use these row numbers to get the dollar values. So in cell L4 we type this formula and autofill it downwards: =VLOOKUP(L$3,INDIRECT(“$E$” & K3+1 & “:$i$1901”),5,FALSE)
Since we are using the fact that the row number of the matched entry has already been found before in column K, a much simpler formula to put in L4 however is: =INDIRECT(“I” & K4)
So to summarise, in order to use VLOOKUP across mutliple cells, you’ll need to:
1. Type the value that you want to look up in a cell.
2. Just to the left of this cell, type the starting row number of your table.
3. Just below this cell in step 2, type this formula: =ROW(INDEX(INDIRECT(“$E$”& K3+1 & “:$i$26”),MATCH(L$3,INDIRECT(“$E$”& K3+1 & “:$E$26”),0),1))
- Replace ‘L$3’ in this formula with the cell reference you entered in step 1. Have the $ signs exactly as it is in the formula.
- Replace all K3’s with the cell reference you entered in step 2.
- Replace all the E’s with the column that holds your matching value. In our case, it was name field in column E. It is usually the first column of your table.
- Replace ‘:$i$26’ with the column that holds the value you want to return. In our, case it was the dollar amount in column I. Make sure you include the colon and the $ signs.
4. Just below the cell you entered in step 1, type this formula: =INDIRECT(“i” & K4)
- Replace “i” with the column that holds the value you want to return.
- Replace K4 with the column reference you entered in step 3.
5. Drag the two formulae down as many cells as necessary (explained below).
How do you know how many cells you should drag your formulae down?
In practice, you don’t normally know. If you keep dragging it more than it is necessary, you’ll see cells with ‘#n
/A’ and ‘#ref
‘ errors. One way to avoid these errors is to wrap the formulae of step 3 and 4 in an IFERROR() function like this:
=IFERROR(ROW(INDEX(INDIRECT(“$E$”& K3+1 & “:$i$26”),MATCH(L$3,INDIRECT(“$E$”& K3+1 & “:$E$26″),0),1)),””)
=IFERROR(INDIRECT(“i” & K4),””)
What this does is that when there is an error, the function returns “” or empty strings indicated at the end of the formula. Remember you could achieve all the above by using the Array formula, which is more compact and easier to type, but they have the drawback of not being very flexible as well as not being very familiar to you.