Index Match function returning wrong values sometimes

Hello, I am trying to use INDEX MATCH with an OR condition but it sometimes returns wrong information. This was my formula:

=IFERROR(INDEX('BE Grades All Terms'!E:E, MATCH(1, ('BE Grades All Terms'!B:B='Full List '!A3)*(OR('BE Grades All Terms'!D:D=11,'BE Grades All Terms'!D:D="11CS")),0)), "")

I need it to select from multiple courses (e.g. 11 or 11CS) and choose the first grade that shows up. Interestingly, when I replace 11 and 11CS with 12 and 12CS I get the same answers, some of which are again wrong. Thanks for any help.

You need to do it like this in C2:

=IFERROR(INDEX('BE Grades All Terms'!$E:$E, MATCH(1, ('BE Grades All Terms'!$B$2:$B$1500=$A2)*(('BE Grades All Terms'!$D$2:$D$1500=11)+('BE Grades All Terms'!$D$2:$D$1500="11CS")),0)), "")

(Use CSE to commit the formula, as it is an array formula).

Note that I have shortened the ranges, as it is not a good idea to use full-column references in an array formula.

Copy the formula down as required.

Hope this helps.

Pete

Thank you both for the help. That formula worked. Do you know why my formula worked (did not result in an error) but provided the wrong answer? Wouldn't the OR function return 1 if either 11 or 11CS were found?

Also, why is using whole column reference comparing 1048576*3 cells? I get the first number from the number of rows in Excel 2007 and up but I don't know where the 3 comes from.

Many thanks

First of all, the AND function and the OR function do not work with array references - you have to specify individual cells like:

=AND(A1,A2)

You can't use AND(A1:A2)

The equivalent functions for arrays are * and + respectively (draw up a simple truth table of 2 variables to see how these are equivalent).

You should also note as well that the MATCH function works on a 1-D array, and that there are just over 1 million cells in each column. So, for the MATCH function to work here, the formula has to evaluate each cell of the three arrays to turn them into one single array. This means that a check is made to see if D2 on the BE Grades sheet is equal to 11 OR if cell D2 is equal to 11CS (which will return either TRUE or FALSE for that element), and at the same time B2 is compared with A2 of the Full_list sheet to see if they are equal, and this result (also TRUE or FALSE) is ANDed with the earlier result to produce a single element of either TRUE or FALSE. This is then repeated for the next element (i.e. D3 and B3), and so on for the range defined for the arrays. In my example I used a range going up to row 1500 to give you a bit of flexibility if you were to add some more data, whereas sktneer's formula used row 1371 which covers your data exactly.

However your example used full-column references, so each of those comparisons detailed above will be carried out for every cell in the range, i.e. over 3 million of them, as opposed to 4500 for my example or just over 4100 for sktneers's, and that is just for one cell that contains the formula. If you then copy the formula down, then the number of comparisons will be multiplied by the number of cells used, so you can see how inefficient the formula will be, and this will make your worksheet appear very sluggish as those calculations are carried out.

Hope this helps.

Pete

Using exact references has made processing time much quicker for my sheets. Thank you Pete for your helpful explanation and insight!