Index Match returning values when none should be returned

Here is the function fixed up.
You cannot add to lookup referenced in the Row part of the Index Function. I had to get around this by using a concatenated column. See column J in sheet Received.

You probably want a formula looking more like this:

Formula:

Please Login or Register  to view this content.

Edit: This is an array formula, it has to be entered with Ctrl + Shift + Enter.

Did you enter the formula with Ctrl + Shift + Enter? You can't just copy the formula as the formula will end with 6 in every other column and 7 in every other column. I just made the two first ones two work, one ending with 6 and one ending with 7 and then I copy/pasted them as a pair. Also the date reference has to skip every other cell (sorry, the merging thing does not help). The layout is quite awkward if you ask me and creating a formula that you can just copy right is difficult. Don't know if you saw that but I made an alternative layout on another sheet in my workbook in post #3.

The 1 in my MATCH formula is not the reason for error (didn't you download the workbook I posted? it works perfectly well there). As you can see there are two statements in the match formula, each one of them can evaluate to either true or false. Both of them has to be true for the match to occur. Normally the AND function would be used to find out if both statements are true. However, this is an array formula and the AND function does not work in array formulas. Instead of using AND I'm multiplying the two logic values with each other and Excel then evaluates TRUE as 1 and FALSE as 0. Hence, the multiplication will do the job of an AND function in this case. Only the rows were both statements are true will generate a 1. Hence, using MATCH(1,xxxxx,0) will give the row number for where both statements are true.

A sumifs function will do the job assuming you're using 2007 or later.

=SUMIFS(Receive!$H:$H,Receive!$C:$C,Recon!D$1,Receive!$F:$F,Recon!$A3) for the quantity or =SUMIFS(Receive!$I:$I,Receive!$C:$C,Recon!D$1,Receive!$F:$F,Recon!$A3) for the cost.