Forum Discussion

Maddy1010's avatar
Maddy1010
Brass Contributor
Oct 28, 2020
Solved

another match/search and return value challenge

I have a new challenge.     The document is attached for ease of understanding.  Basically, I'm trying to retrieve data from the Search tab and return the value in the other tab.   What combinati...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Oct 29, 2020

    Maddy1010 

    Sorry, my mistake. It should have been

    =SUMIFS(INDEX(Sheet4!$B$13:$K$46,0,MATCH(C$2&C$3,Sheet4!$B$10:$K$10,0)),Sheet4!$A$13:$A$46,$A10,Sheet4!$L$13:$L$46,$A$8&$A$9&C$1)/1000
HansVogelaar's avatar
HansVogelaar
MVP
Oct 28, 2020

Maddy1010 

In C48:

=SUMIF(Search!$A$2:$A$37,$A48,INDEX(Search!$B$2:$Q$37,0,MATCH(C$2,Search!$B$1:$Q$1,0)))

Fill to the right to column F, then down to row 50 (or vice versa).

In H48:

=SUM(C48:F48)

Fill down to row 50.

  • Maddy1010's avatar
    Maddy1010
    Brass Contributor
    Oct 28, 2020

    HansVogelaar Thanks for this!  It worked again.  

    Just to add complexity -- I would like to add a 'check'  by adding a Total (Row 52) vs =sum function.  Filtering the data, I can trust Row 52 -- but now, the 53, 54 and 55 are not following.  

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 28, 2020

      Maddy1010 

      I'm afraid I don't understand what you are trying to do now. Can you explain?

      • Maddy1010's avatar
        Maddy1010
        Brass Contributor
        Oct 28, 2020

        HansVogelaar  Hi Hans, sorry I wasn't clear at all.  

         

        Basically, rows 53 and 54 in total should equal row 52, with a formula that would match column A and column C, row 1, row 6, etc.  

         

        Something like, if 'CC_ABCD - Apples' & 'All other OPEX' & '2018.Q1' matches data in Search tab in Column S, then return value.  Sorry, I'm not good at this.

         

         

         

Resources