VLOOKUP on Two Columns
Here are three ways to lookup a value based on two columns. Let’s start with this random data:
And let’s say that we want to lookup the autonumber value for Carlo. Not the Carlo in Seaford, but the Carlo in Westwood.
SUMPRODUCT
With ‘Carlo’ in E2 and ‘Westwood’ in F2, use =SUMPRODUCT(($A$2:$A$16=E2)*($B$2:$B$16=F2)*(C2:C16))
If you have more than one Carlo/Westwood combination, you’ll get the sum of all of them. One advantage of this method is that you don’t need any helper columns. If you can’t mess with your data, it’s a good option. However, if you have a lot of these formulas, it can become slow.
VLOOKUP
To use VLOOKUP, we’ll need to add a column on the left. VLOOKUP only works when the look up column is the first column. In the new column A, I concatenated columns B and C.
This is quicker than SUMPRODUCT, but you won’t see the difference until you have a lot of formulas. The downside, obviously, is that you have to add another column on the left.
INDEX
For the INDEX method, you still have to add a new column, but it doesn’t have to be the first column.
Same benefits and detriments as the VLOOKUP option, but you can tack that extra column on the end.
You can also use:
INDEX($A$2:$D$16,MATCH(1,(F2=$A$2:$A$16)*(G2=$B$2:$B$16),0))
You need to enter it as an array formula. It’s similar in nature to the sumproduct, but sumproduct doesn’t work if the figure that you’re trying to look up isn’t a number.
Quibble: concatenating fields without explicit field separators can occasionally lead to unexpected (wrong) results.
There’s no need for string concatenation, which is usually slow. Use an array formula.
=INDEX(A2:C16,MATCH(TRUE,IF(B2:B16=G2,A2:A16=F2),0),3)
Or if the last match is as good as the first match, use the regular formula
=LOOKUP(2,1/(B2:B16=G2)/(A2:A16=F2),C2:C16)
Although slower, the SUMPRODUCT approach allows a few other advantages:
- Greater flexibility in the number and variety of criteria you can use in selection; the VLOOKUP/INDEX approach can get messy if you have 5 different combinations of data that you need for selections
- The SUMPRODUCT approach can provide both SUMIF and COUNTIF functionality that the VLOOKUP/INDEX approaches do not
The glaring problem is that sumproduct will only return results if you are looking up a numeric value. If column C in your first example had alphanumeric data (as you might want from a vlookup), it would give you a #VALUE!.
I don’t have a solution to that.
But if you are looking at just numbers and have XL07, I recommend the new SUMIFS formula. It is dramatically faster than using SUMPRODUCT or an array formula.
Treat the table as the source for a SQL query.
All good points. I need a pipe seperater in there.
Tushar: I’ve done it, but I don’t trust it. I trust the results, but it seems like it could break at any minute. Has anybody done any good testing on it?
Dick: I have used SQL queries for “production” systems. The Excel worksheets have had several thousand rows of data and some queries included joins across tables in different worksheets.
I have also queried data in corporate databases to extract the data into Excel.
What I sometimes do is use the query for the initial analysis, then use that as the source for further analysis, which might be some kind of “roll your own” result or a PivotTable or Chart.
I have had no problems with this approach.
You can use a SUMPRODUCT to return a text result from a Multi-criteria lookup. Someone asked me for a multicriteria vlookup here it is
=INDEX(C2:C7,SUMPRODUCT(MATCH(TRUE,(A2:A7&B2:B7)=(F2&G2),0)),1)
It finds the first instance only. I had never used a MATCH within a SUMPRODUCT, but it seems to work ok.
Another example of the flexibility of the humble SUMPRODUCT function.
You could also use a combination of INDEX and MATCH without a helper column. Needs to be array-entered:
=INDEX(C2:C16,MATCH($E$2&”|”&$F$2,$A$2:$A$16&”|”&$B$2:$B$16,0))
I used SQL queries a lot in certain Excel models, with success. Officialy, it’s documented as a technology to use on closed workbooks, but it works fine on open workbooks. It does give me sometimes an I/O error when closing the file after debugging. But it works like a charm in production.
i like this rray formula:
=INDEX($C$2:$C$16, MATCH(1, ($A$2:$A$16 = F2) * ($B$2:$B$16 = G2), 0))
If you use Excel 2007, then you can convert the range into a Table, and the array formula looks nicer, and grows without fancy dynamic ranges:
=INDEX(Table1[AutoNumber], MATCH(1, (Table1[First Name] = F2) * (Table1[City] = G2), 0))
In the past I was a power user of SQL & Excel Workbooks. However, after having some seriously issues with memory leak with one client’s solution I now use it less.
Kind regards,
Dennis
If you know the list to be unique and you’re still looking up a number, you could use SUMIFS or AVERAGEIFS in 2007. On a table, the formula would be:
=SUMIFS(tbl[AutoNumber],tbl[First Name],F2,tbl[City],G2)
Very readable and much faster than SUMPRODUCT.
To avoid the CSE.
=INDEX($C$2:$C$16, MATCH(1, INDEX(($A$2:$A$16 = F2) * ($B$2:$B$16 = G2),0), 0))
Regards
Interesting question whether both ($A$2:$A$16 = F2) * ($B$2:$B$16 = G2) and IF(B2:B16=G2,A2:A16=F2) would both perform all 30 string comparisons, or whether the latter only performs A2:A16=F2 comparisons when the corresponding value in B2:B16 equals G2. If there are fewer comparisons in the latter, an array formula approach using the latter may yield quicker recalc when the ranges involved span hundreds or thousands of rows.
Hmm.. am I the only one still using dimensional aggregates?
Seems to me a simple DGET formula would yield the correct result.
” syntax =DGET(database, field,critera)
” example =DGET($a$1:$c$16,$c$1,$f1:$g2)
Cells F1 and G1 would need corresponding column headings from A1 & B1
I use these a lot when aggregating specific metrics using multivariant criteria.
They are cpu hungry though when looking at large flat file databases.
Neal,
Your approach of using Match inside Sumproduct is great - Avoids array entering. The only change would be Sumproduct((Match(1,(Cnd1)*(Cnd2),0),1)
instead of the concatenate string approach
Justinb: Yep, you are.
I don’t use them because I don’t like criteria ranges, but otherwise have no problem with them.
It upsets me when people resist array formulas. I guess it upsets some people when I resist domain functions
Dick Kusleika -
I found it interesting that Excel 2007 actually won’t allow the first parameter of vlookup to be anything more than a cell reference.
fzz - Thanks! Needed a “simple” array formula for a simple task of breaking out spending for individual project line items over different blocks of months between project start and stop. Yours is perfect.
How to use a VB split multiple rowss based on “,” in a cell
Doesn’t anyone like =DGET?
excel has too many options for too many users…
Awesome solutions to the problem which I had for weeks. Thanks a ton.