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.
With ‘Carlo’ in E2 and ‘Westwood’ in F2, use
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.
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.
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.