SOLVED

# another match/search and return value challenge

Occasional Contributor

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

16 Replies

# Re: another match/search and return value challenge

With the attachment.

# Re: another match/search and return value challenge

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.

# Re: another match/search and return value challenge

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

# Re: another match/search and return value challenge

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

# Re: another match/search and return value challenge

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

# Re: another match/search and return value challenge

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

# Re: another match/search and return value challenge

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

# Re: another match/search and return value challenge

@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

# Re: another match/search and return value challenge

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.

# Re: another match/search and return value challenge

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

# Re: another match/search and return value challenge

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.

# Re: another match/search and return value challenge

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``

# Re: another match/search and return value challenge

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?

# Re: another match/search and return value challenge

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

Best Response confirmed by Maddy1010 (Occasional Contributor)
Solution

# Re: another match/search and return value challenge

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``

# Re: another match/search and return value challenge

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