Jul 29, 2014
Excel‘s #vlookup 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 Substituting yields; ROW(INDEX($E$4:$i$26,MATCH(L$3,$E$4:$E$26,0),1)) 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.
- Replace “i” with the column that holds the value you want to return.
- Replace K4 with the column reference you entered in step 3.
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.=IFERROR(ROW(INDEX(INDIRECT(“$E$”& K3+1 & “:$i$26”),MATCH(L$3,INDIRECT(“$E$”& K3+1 & “:$E$26″),0),1)),””)
=IFERROR(INDIRECT(“i” & K4),””)
How do your Excel skills stack up?
Test NowNext up:
- Assertiveness – From theory to practice
- Become an ace in Microsoft Azure SQL Database
- Creating multiple signatures in Outlook
- Get online with Lync Online
- Keep your Excel formulas in place with dynamic named ranges
- MH-17 and words
- Run the Runbook Tester in System Center 2012 R2
- Easily delete blank rows from your data using Excel VBA
- Synchronising concurrent access to data in C#
- The enhanced Presenter View in PowerPoint 2013
Previously
- Get your head into the cloud for free!
- Remove excess spaces from data in Microsoft Excel
- Crash course in Microsoft Azure SQL Database
- Interviewing and avoiding the artful dodger!
- Access your Access files in Microsoft Project
- Networking architecture in Lync Server 2013
- 10 essential keyboard shortcuts in Photoshop
- Remove blank rows in Excel with this VBA code
- Becoming a great workplace trainer starts with three words (Part 3)
- Cross-site publishing with SharePoint 2013