Multi-criteria Index Match is not looping through all Criteria.

[RockandGrohl]

Good afternoon all,

This flawless formula I discovered on ExcelJet is now giving me a bit of grief after months of working in various applications.


Here's my code:

=IFERROR(INDEX(LEFT('[Price Panels 2019.xlsm]All Tours'!$A:$A,6),MATCH(1,(A3<>'[Price Panels 2019.xlsm]All Tours'!$A:$A)*(H3='[Price Panels 2019.xlsm]All Tours'!$C:$C)*ISNUMBER(SEARCH(G3,'[Price Panels 2019.xlsm]All Tours'!$K:$K)),0)),"")

An explanation:

This is returning a code held in Price Panels on the following criteria:

Current Code (A3) can not match anything in Column A in Price Panels (This is the new line giving me grief)
Tourname (H3) must match Tourname in Price Panels column C
Pickup (G3) must match pickup in Price Panels column K (this is held in a long list for each row, hence the pickup)


This was working without the first criteria, but the issue was it would just return the same result as I was putting in.

To clarify, this looks at people who want to book a tour on a date, but it's full. This system then looks at Price Panels (our huge list of products and availability) and scans down the list to find the same tour on a later date.

The multi-criteria Index Match syntax is as follows:

[COLOR=#FF66FF]{=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))}[/COLOR]

So any criteria's can be added with the simple addition of another "*(criteria)"

This should convert the whole lot to, like, "0,1,1" to say not a match, and only "1,1,1" should be a match.


My problem is the formula is sometimes returning a correct match and other times not.