Contents
Formulation of the problem
If you are an advanced user of Microsoft Excel, you should be familiar with the search and substitute function. VPR or VLOOKUP (if not yet, then first read this article to become one). For those who understand, there is no need to advertise it 🙂 – not a single complex calculation in Excel can do without it. There is, however, one problem: this function can only search for data by matching one parameter. What if we have several?
Suppose we have a database of product prices for different months:
You need to find and pull out the price of a given product (Nectarine) in a certain month (January), i.e. get the output152, but automatically, i.e. using a formula. VLOOKUP in its purest form will not help here, but there are several other ways to solve this problem.
Method 1: Additional column with lookup key
This is the most obvious and simple (although not the most convenient) way. Since the regular function VPR (VLOOKUP) can only search in one column, and not in several, then we need to make one out of several!
Let’s add another column next to our table, where we glue the name of the product and the month into a single whole using the concatenation operator (&) to get a unique key column for searching:
Now you can use the familiar function VPR (VLOOKUP) to search for a glued pair NectarineJanuary from cells H3 and J3 in the generated key column:
Pros: Easy way, familiar function, works with any data.
Cons: We need to make an additional column and then, perhaps, also hide it from the user. When changing the number of rows in the table, extend the concatenation formula to new rows (although this can be simplified using a smart table).
Method 2: SUMIFS function
If you need to find exactly the number (in our case, the price is just a number), then instead of VLOOKUP, you can use the function SUMMESLIMN (SUMIFS), which appeared since Excel 2007. In theory, this function selects and sums numeric values for several (up to 127!) conditions. But if our list does not contain duplicate products within one month, then it will simply display the price value for a given product and month:
Pros: No additional column needed, the solution easily scales to more conditions (up to 127), counts quickly.
Cons: Only works with numeric output, not applicable for text search, does not work in older versions of Excel (2003 and earlier).
Method 3: Array Formula
About how to use a bunch of functions INDEX (INDEX) и MORE EXPOSED (MATCH) as a more powerful alternative to VLOOKUP, I have already described in detail (with video). In our case, we can use them to search across multiple columns as an array formula. For this:
- Highlight the empty green cell where the result should be.
- Enter the following formula in the formula bar:
- Press at the end not Enter, but a combination Ctrl + Shift + Enterto enter the formula as an array formula instead of a regular formula.
How it actually works:
The INDEX function returns the contents of the Nth cell in order from the price range C2:C161. In this case, the serial number of the desired cell is found by the MATCH function. She looks for a bunch of product name and month (NectarineJanuary) in turn in all cells of the range A2:A161&B2:B161 glued from two columns and gives the ordinal number of the cell where it found an exact match. In fact, this is the first way, but the key column is created virtually right inside the formula, and not in the cells of the sheet.
Pros: No need for a separate column, works with both numbers and text.
Cons: Significantly slows down on large tables (like all array formulas, however), especially if you specify ranges “with a margin” or whole columns at once (i.e. instead of A2: A161 enter A: A, etc.) Many are unusual formulas array in principle (then you are here).
- How to search and substitute data using the VLOOKUP function
- What are array formulas and how to use them
- How to use a combination of INDEX and MATCH functions instead of VLOOKUP
- How to extract all values at once, not just the first one using VLOOKUP