another match/search and return value challenge

[Maddy1010]

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 combination of functions can I use?  Thanks (again!).

@Hans Vogelaar 

[#2]

With the attachment.

[#3]

@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.

[#4]

@Hans Vogelaar 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.  

[#5]

@Maddy1010 

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

[#6]

@Hans Vogelaar  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.

[#7]

@Maddy1010 

There is no reason rows 53 and 54 should add up to row 52.

The formulas in row 53 and 54 sum all values on the Search sheet for Apples and Bananas, but those rows have both IHQInternational ManagementHR / Staff Costs and IHQFruitsAll other OPEX in column R.

The formulas in row 52 sum the values for IHQFruitsAll other OPEX only, not those for IHQInternational ManagementHR / Staff Costs

[#8]

@Hans Vogelaar 

So, rows 53, 54 (and 55 if with values), should add up to row 52, in case Column R in Search tab says IHQFruitsAll other OPEX.  So the qualifier will be IHQFruitsAll other OPEX.  

If I filer Column R in Search tab, the totals in Column E in Search tab would be 547.9 (divided between CC_43598 - Bananas and CC_ABCD - Apples.)

[#9]

@Hans Vogelaar I promise, I'm doing my best to express my thoughts :-).

If A53 in Return Value matches Column A in Search, C1 in Return Value matches Column R in Search, C6 in Return Value matches Row 2 in Search, then return value in Search tab range B4: Q38

[#10]

@Maddy1010 

In C53:

=SUMIFS(INDEX(Search!$B$3:$Q$38,0,MATCH(C$6,Search!$B$2:$Q$2,0)),Search!$A$3:$A$38,$A53,Search!$R$3:$R$38,$A$5&$A$52&C$1)/1000

Fill to the right and down.

[#11]

@Hans Vogelaar This is pure witchcraft.  You solved that in seconds.  

[#12]

@Hans Vogelaar 

I am attempting to fill cells C10:M12.  Qualifiers are:  Rows 1,2,3 and Column A -- all in Sheet3.  Sheet4 has all the data.  

I tried a combination of the formulas you gave me so far, but it doesnt seem applicable on this one since I added a new 'qualifier'  which is Row 2.

[#13]

@Maddy1010 

In C10:

=SUMIFS(INDEX(Sheet4!$B$13:$K$46,0,MATCH(C$2&C$3,Sheet4!$A$10:$K$10,0)),Sheet4!$A$13:$A$46,$A10,Sheet4!$L$13:$L$46,$A$8&$A$9&C$1)/1000

[#14]

@Hans Vogelaar 

This time, I literally copy/pasted the formula onto my test sheet but the answer is not coming up correctly.

I was expecting Row 10,11,12 to add up to Row 9.  What am I doing wrong?

[#15]

@Hans Vogelaar 

It would seem, that somehow, the values are picking up the next column other than intended:  I really don't understand how come.

[#16 · Accepted solution]

@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

[#17]

@Hans VogelaarIt worked!!!!  Thank you!!!