Index/Match Multiple Criteria not accurate

There's a few ways I'm sure. Here's the first one that comes to mind:

=LOOKUP(2,1/(('Answer Data Month 1'!BA2:BA15000=Analysis!A2&"-"&Analysis!B2)*('Answer Data Month 1'!BC2:BC15000="Correct")),'Answer Data Month 1'!BB2:BB15000)

Lookup is the predecessor of the modern VLookup and HLookup, so instead of looking up the first row or column and returning the Nth, you have to explicitly state which to look in and which to return.

Parameters and use:
=LOOKUP(lookup what, where, return from where)

=LOOKUP(1,{1,2,3},{4,5,6}) returns 4. It looked up the 1, found it in the first range of 1,2,3 and returned the first range of 4,5,6.

Notes:
LOOKUP, like it's more current relatives, ignores all errors in the range it's searching for.

=LOOKUP(2,{#DIV/0!,2,3},{4,5,6}) = 5

LOOKUP is always using an approximation find. It will key to the closest match that doesn't exceed its value.

=LOOKUP(2,{1.5,2.5,3.5},{4,5,6}) = 4

LOOKUP is returns the last match of many matches

 =LOOKUP(1,{1,1,1},{4,5,6}) = 6

Using this knowledge, we can combine those extra features to do some fancy stuff that we can't do this with the more evolved versions, such as nesting expressions into parameters.

Assume the following ranges of data: A1:A10 = 1, 2, .. 10 and B1:B10 = 1, 1, 2, 2, ...5 etc. and C1:C10 = something you want to return.

=LOOKUP(2,1/((A1:A10=4)*(B1:B10=2)),C1:C10)

Find what:
2

Find it where:
1/((A1:A10=4)*(B1:B10=2))

Return what:
C1:C10

First we calculate the nested expression:
(A1:A10=4) = {False,False,False,True,False,False,False,False,False,False}
(B1:B10=2) = {False,False,True,True,False,False,False,False,False,False,}

When True and False are exposed to operators (+, -, /, *) Excel coerces them into 1 and 0.

{False,False,False,True,False,False,False,False,False,False}*{False,False,True,True,False,False,False,False,False,False,}

becomes

{0,0,0,1,0,0,0,0,0,0,0}*{0,0,1,1,0,0,0,0,0,0}

This is how all array criteria logic works. The matrix of ranges are applied against each other at like-position.

{0,0,0,1,0,0,0,0,0,0,0}*{0,0,1,1,0,0,0,0,0,0} = {0,0,0,1,0,0,0,0,0,0,0}

1/{0,0,0,1,0,0,0,0,0,0,0} is evaluated next to become {#DIV/0!,#DIV/0!,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}

With the expression fully calculated, we return the to function.

=LOOKUP(2,{#DIV/0!,#DIV/0!,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},C1:C10)

Now we look for 2 in a series of Errors and 1's and it won't find it. It will approximate instead, to the *LAST* 1. That 1's position is then returned from C1:C10.

Summary!

• The 2 is arbitrary. Any number bigger than our evaluated expression works fine. 42 could just as easily be used.
• We pass nest all criteria within parenthesis so they fully evaluate against each other.
• Next, we divide 1 by the result of our expression, to create errors intentionally.
• The 2 matches to the non-errors, and returns the last match in the return index.