Index/Match Multiple Criteria not accurate

[Groovicles]

hi everyone,

I have the following formula:

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

Right now, the output is not taking the 2nd criteria "Correct" into account when displaying the answer. How can i adjust the formula to have this second criteria reflected as well?

Thanks!!

[daffodil11]

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

Please Login or Register  to view this content.

[Groovicles]

Hi daffodil11,

Thanks very much; your formula works perfectly. I'm not familiar with the Lookup function and a few of the sections in your formula baffle me. If you have a moment, could you explain how the function works? Specifically, I don't under the first part =lookup(2,1/((.......What does this mean?

Thanks!

[daffodil11]

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

LOOKUP is returns the last match of many matches

Please Login or Register  to view this content.

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.