For example, there is data like the picture below, how much is the price of an Apple iPhone XS?
What is the formula to find the price, there are two criteria, manufacture=”Apple” and Model=”iPhone XS”. Is it possible to use the VLOOKUP function or is there another solution?
If the lookup result is a number then forget the VLOOKUP function, you can use mathematical functions and produce a simpler formula, one of them is the SUMIFS function.
The result is $1,340
Another mathematical function that you can use to look up a number with multiple criteria is the SUMPRODUCT function.
The last solution is to use the SUM function, not the usual SUM function but the SUM function that processes an array formula.
Because it is an array formula, you can’t end the writing of the formula by pressing the ENTER button but by pressing the CTRL+SHIFT+ENTER button simultaneously.
For example, there is data like the picture below, where does Evelyn Thao live?
The limitation of the VLOOKUP function is only lookup for one condition, that is lookup_value. If there are two or more criteria the easiest way is to concatenate all the criteria in the lookup_value.
The next step is to create the helper column that contains a merge of two columns in table_array. The helper column will be the first column of table_array, where the VLOOKUP function will look for lookup_value that has been merged from two criteria.
The Formula for Helper Column
The Formula to Lookup the State
The solution #B uses the helper column too. The difference is solution #A using a worksheet for the helper column location while solution #B using a formula for the helper column location.
The CHOOSE function will occupy the table_array argument of the VLOOKUP function; the role is to create a virtual helper column in the formula. The virtual helper column consists of two columns. The first column contains a combination of first name and last name. The second column contains the name of the state.
Because it is an array formula, end the formula writing by pressing the CTRL+SHIFT+ENTER button simultaneously.
No helper column in the worksheet but the VLOOKUP function can do vlookup with two criteria.
The solution #C is similar to solution #B, creating a virtual helper column in the formula. If the solution #B uses the CHOOSE function to form a virtual helper column. Solution #C uses the INDEX and MATCH functions where the MATCH function is assigned to create a virtual helper column.
End the formula writing by pressing CTRL+SHIFT+ENTER simultaneously, because the formula above is an array formula
For Numeric Result
The SUMIFS function is the best solution, simple and answers the questions. If the SUMIFS function is not available in your excel (seriously? you should upgrade your excel version 😊), then you can use the SUMPRODUCT function, or you can use SUMIF with multiple criteria.
For Non-Numeric Result
The easiest solution is to use the VLOOKUP function and the helper column. If you want no helper column in the worksheet, then you can use the virtual helper column in the formula.
There are two options, The VLOOKUP and CHOOSE function or INDEX and MATCH function, both is an array formula. Choose the most comfortable formula for you.