If you use 2 as the lookup value, then the formula will match it with the last numeric value in the range, that is, the last row where both conditions are True. This will return either 1 or a #DIV/0! error. What this formula does, is divide 1 by an array of True/False values (B3:B13=C16), and then by another array of True/False values (D3:D13=C18). The result will be the same as in the previous solution. You could also do this using a different technique, such as this formula in cell C17: Then you use SUMPRODUCT in the INDEX function to return the value in the array C3:C13 that is in the row number provided. You use the SUMPRODUCT function to find out the row where both criteria are met, and return the corresponding row number using the ROW function. This formula would give us a #VALUE error! Instead, you could use a formula using a combination of SUMPRODUCT, INDEX and ROW functions, such as this one: You would be looking for the Name “James Atkinson” where the Qty is 1, and you’d like to return the Product name that matches these two criteria. This technique cannot be used if you want to look for two criteria and return a text result. Let’s change the value in cell C5 from “Wine Bottle” to “Milk Pack” to see what happens with the formula in cell C18:īecause our formula found two lines where both criteria were met, it sums the values in column D in both rows, giving us a Qty of 6. It returns the value 1, which corresponds to the value in cell D4 (“James Atkinson” in row 4 and also “Milk Pack” in the same row), thus returning the value in column D from that row. When it finds both, it returns the value in column D, from the same row where it met both criteria. What it does is look in the range B3:B13 for the value in cell C16, and in the range C3:C13 for the value in cell C17. The SUMPRODUCT formula in cell C18 looks like this:
Because the value that you want to return is a number, you can use a simple SUMPRODUCT() formula to look for the Name “James Atkinson” and the Product “Milk Pack” to return the Qty. The criteria are “Name” and “Product,” and you want them to return a “Qty” value in cell C18. Let’s look at a scenario where you want to use two criteria to return a value. Get Excel Using two criteria to return a value from a table
0 kommentar(er)
