Forum Discussion

Cmpunk's avatar
Cmpunk
Copper Contributor
May 05, 2022

Excel Query!!!

Hello All,

 

Suppose I have two tables. One table has a date and on another table, there is a date, animal, and weight

 

      
   Cat1/31/200712
1/31/2007  Cat2/28/200715
2/28/2007  Cat3/31/200716
3/31/2007  Cat4/30/300717
   Dog1/31/200718
   Dog2/28/200719
   Dog3/31/200721
   Dog4/30/300722

 

How to take into account that date belongs to Cat or dog and then perform VLOOKUP?

 

 

 

excel

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 05, 2022

    Cmpunk 

    See the screenshot:

    The formula in B3 is

     

    =INDEX($F$2:$F$9,MATCH(1,($D$2:$D$9="cat")*($E$2:$E$9=A3),0))

     

    If you don't have Microsoft 365or Office 2021,confirm the formula with Ctrl+Shift+Enter.

    Fill down to B5

    • Cmpunk's avatar
      Cmpunk
      Copper Contributor
      May 05, 2022

      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/20072
      2/28/20073
      3/31/20074 
        
      AnimalDate 234
      Cat1/31/2007 0.60.30.3
      Cat2/28/2007 0.50.20.2
      Cat3/31/2007 0.40.50.5
      Dog1/31/2007 0.30.30.4
      Dog2/28/2007 0.20.20.2
      Dog3/31/2007 0.50.50.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?

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 05, 2022

        Cmpunk 

        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.

Resources