Multi index match with find or search function

[proficient]

Hi all, I have a table containing receipt (Column A) amount (Column B) description (Column C).

The most important thing is that the receipt multi receipt numbers but by description cloumn we can identyfy as if one receipt # used to purchase chair it won't be use to purchase chair again.

Descriptions are like this
Amount paid for Chair
Amount paid to Load man
Amount paid to client

The next table where I want to get the answer is something like this;
Receipt # (under this I put manually receipt numbers
And in every next cells of receipt chair, load man and client are put in different cells.
I want to search the chair, load man and client amount next to their relative receipt #.

 

[proficient]

Help.

 

[proficient]

Anyone can help me? It's really urgent.

 

[Marcelo Branco]

Could you provide a small data sample (5 to 10 rows) along with expected results?

M.

 

[proficient]

https://www.dropbox.com/s/twtg1yvzpc8hb40/IMG-20171201-WA0000.jpg?dl=0

Here is the sample data for your ease.

 

[proficient]

=sumproduct(--(isnumber(search($a13&b$12,$a$2:$a$8&$c$2:$c$8))),$b$2:$b$8)
I tried this formula but getting ZERO

 

[Misca]

Not sure I can understand what you're after but if you want to see if there's already an amount for the receipt # with matching words in description you could do that with something like:

=SUMPRODUCT(--($A$2:$A$8=$A13),--ISNUMBER(SEARCH(B$12,$C$2:$C$8)),$B$2:$B$8)

The formula sums all the amounts where the receipt # is found in Column A and the keyword to look for in description is in Row 12 (= the same setup as in your sample). If you don't want the amounts leave the last bit out and you'll get the number of matching rows.

 

[proficient]

Works like a charm, thank you very much, one more question regarding parenthesis you used in the formula, how can I understand where to use single or couple of parentheses...?

 

[Misca]

I can't think of a single situation where I'd need a couple of parenthesis. You wrap the elements of a function inside one and if you want to alter the calculation order or do mathematical operations on a group of values / cells you wrap those inside one as well but I always think of them as a single parenthesis.

I try change the colors of each set of parenthesis in my formula suggestion:


=SUMPRODUCT(--($A$2:$A$8=$A13),--ISNUMBER(SEARCH(B$12,$C$2:$C$8)),$B$2:$B$8)

As you can see there's always just a single parenthesis, though sometimes they can start / end right next to each other.

The double negatives stand for (-1)*(-1)* and they're used to turn the boolean values (= TRUEs and FALSEs) to ones and zeroes. The red parenthesis are needed because you want to get the boolean values before the multiplication. Without them you'd be just multiplying the range, not the comparison results. The ISNUMBER-function already returns the booleans so you don't need to wrap it inside another set of parenthesis.

And since the last bit of the formula already returns numbers (= amounts) you don't need to do anything to them.

If you're not sure if a pair of parenthesis is needed or not, I'd suggest you use them just in case: Extra ones don't hurt the formulas but if you don't use one when it's needed, the formula will not work as it should: it still might return a value but not the correct one.

 

[proficient]

Thank you so much, you explained well.