The Solution for Numeric Result
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?
Solution #1, SUMIFS Function
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 Formula
=SUMIFS(C2:C14,A2:A14,F3,B2:B14,F4)
The Result
The result is $1,340
For additional reference, please read
Solution #2, SUMPRODUCT Function
Another mathematical function that you can use to look up a number with multiple criteria is the SUMPRODUCT function.
The Formula
=SUMPRODUCT(C2:C14,--(A2:A14=F3),--(B2:B14=F4))
The Result
For additional reference, please read
Solution #3, SUM Function and Array Formula
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.
The Formula
=SUM((C2:C14)*(A2:A14=F3)*(B2:B14=F4))
The Result
For additional reference, please read
The Solution for Non-Numeric Result
For example, there is data like the picture below, where does Evelyn Thao live?
Solution #A, VLOOKUP Function and Helper Column
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
=B2&C2
The Formula to Lookup the State
=VLOOKUP(G3&G4,A2:D10,4,FALSE)
The Result
For additional reference, please read
Solution #B, VLOOKUP Function, CHOOSE Function, and Array Formula
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.
The Formula
=VLOOKUP(F3&F4,CHOOSE({1,2},A2:A10&B2:B10,C2:C10),2,FALSE)
Because it is an array formula, end the formula writing by pressing the CTRL+SHIFT+ENTER button simultaneously.
The Result
No helper column in the worksheet but the VLOOKUP function can do vlookup with two criteria.
For additional reference, please read
Solution #C, INDEX Function, MATCH Function, and Array Formula
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.
The Formula
=INDEX(C2:C10,MATCH(F3&F4,A2:A10&B2:B10,0),1)
End the formula writing by pressing CTRL+SHIFT+ENTER simultaneously, because the formula above is an array formula
The Result
For additional reference, please read
Which One is the Best Solution?
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.
You can use all solutions for the non-numeric result to look up the numeric result with multiple criteria, but not otherwise. TRY IT AT HOME 😊
Practice File Download
Download File
