Index match help

Hi -

I don't see an attached file. Sometimes the file attachment icon doesn't work. So you have to use the "Go Advanced" button on the lower right corner of the post window. When the Advanced window shows up, scroll down to "Manage Attachments", select that and upload your attachment from there.

Hi -

Use SUMIFS instead. Looks like this:

=SUMIFS($C$2:$C$5,$A$2:$A$5,A16,$B$2:$B$5,B16)

One other thing - If you end up with more that one entry of APPLE and RED, SUMIFS will add up each time it's a match, whereas an INDEX/MATCH formula will only bring back the first instance you have two matches.

It's kind of like using a hammer to break a board in two when you really should use a saw. It can be done with a hammer, but it's going to be messy and a lot of effort.

Hi -

You could probably force INDEX/MATCH to work, probably as an array formula, but it is not the best for searching multiple criteria, especially when there are multiple matches within each list. For example, MATCH will return the first row that it hits APPLE, unless you construct it inside an array formula to count the number of instances that APPLE are included in the list and then search the second criteria which could be "Red", keeping track of which row the desired apple is in that matches with Red. To complicate it further, there are two instances of Red in the second list. It just requires a lot of programming and very complex formulas when you have ready made functions (SUMIFS, COUNTIFS, SUMPRODUCT, etc.) that are designed for exactly this type of problem.

Does that help? Let me know if you have more questions.

You're very welcome! If you are satisfied with this answer, please don't forget to mark this thread as SOLVED per the instructions at the bottom of this post.