Forum Discussion
Excel Query!!!
HansVogelaar Thank you for your response. I have a few questions.
In the match formula, the first parameter is lookup_value I do not understand why you inputted 1 there? Could you please explain? Also, refer to the below table
| 1/31/2007 | 2 |
| 2/28/2007 | 3 |
| 3/31/2007 | 4 |
| Animal | Date | 2 | 3 | 4 | |
| Cat | 1/31/2007 | 0.6 | 0.3 | 0.3 | |
| Cat | 2/28/2007 | 0.5 | 0.2 | 0.2 | |
| Cat | 3/31/2007 | 0.4 | 0.5 | 0.5 | |
| Dog | 1/31/2007 | 0.3 | 0.3 | 0.4 | |
| Dog | 2/28/2007 | 0.2 | 0.2 | 0.2 | |
| Dog | 3/31/2007 | 0.5 | 0.5 | 0.3 |
The solution that you provided is very good but currently I am using vlookup so my question is how to incorporate this in the formula. Also kindly refer to the above two tables. I need to use date from the first column and take into account that date belongs to Cat or dog but also make sure that 2 in the first table matches to the 2 in the second table . So if I choose1/31/2007 cat 2 I should get 0.6?
Currently I am using below formula
VLOOKUP(C14,$G$13:$J$19,MATCH(D14,$G$13:$J$13,0)) but it is not taking into account whether it is a dog or cat?
I didn't use VLOOKUP because you have multiple criteria: date and animal.
($D$2:$D$9="cat")*($E$2:$E$9=A3) is an array of 0 /1 values: 1 for a match, 0 for a non-match.
We use 1 as lookup value to find the index of the first 1, then use that in INDEX.
For your new request, I have attached a sample workbook with two possible formulas: one using INDEX and MATCH, the other using SUMPRODUCT.