Problem with a conditional MATCH()

Question

I have a problem with a conditional MATCH() formula and can not understand why it is not working!

Assume that the spreadsheet is set out as follows:

Cells A2 to A82 contain a value of between 1 and 9

Cells B2 to J82 will contain a number of values also between 1 and 9. These are created by formula so that column B will always have a 1 or a blank (""), column C will always have either a 2 or a blank(""), column D will always have a 3 or a blank("") and so forth through to column J always having either a 9 or a blank(""). 

I have a formula in cell K3 designed to provide the MATCH() value for the first instance of a given number in column B (specified in K1) for a given number in column A (specified in K2) as follows:

=MATCH(K1,IF($A$2:$A$82=K2,INDEX($B$2:$J$82,0,K1)),0)           [entered as an array formula using CTRL-SHIFT-ENTER].

This provides the relative position of the first instance of value K1 in the correct column where the values in A2:A82 are the same as K2.

I now need to do the same again, but to find the second instance and have tried using:

=MATCH(K1,IF($A$2:$A$82=K2,OFFSET($A$1,K3+1,K1,81-K3,1)),0)          [also entered as an array formula using CTRL-SHIFT-ENTER].

This does not work as intended. In trying to debug, I have changed the formula to specify the range provided by the OFFSET and the formula works, I have also changed the formula so that it simply sums the range provided by the OFFSET to check that this is working and this is also fine, but the two elements will not work together.

Please help!!

Answer 1

FALSE is the equivalent of 0 (aka zero). Since you are looking for the SMALL() (or MIN()) ROW(), the first match would be any FALSE since rows start at 1 and you are returning a 0. Returning 1E+99 when FALSE will never match a MIN() or SMALL() when looking for a ROW() as it is larger than the largest ROW() number on any worksheet.

Put another way, if you run the array formula through the Formula tab's Evaluate Formula, you will see the array of TRUE and FALSE turn to row numbers and 1E+99's. Remove the 1E+99's and you get row numbers and zeroes. A MIN() or SMALL() will always return a zero before a valid row number.

Answer 2

First off, here are two alternate standard formulas for K3 that do not require Ctrl+Shift+Enter to finalize.

=MATCH($K1,INDEX(($A$2:$A$82=$K2)*($B$2:$J$82),,$K1),0)

... or,

=MATCH($K$1,INDEX(($A$2:$A$82=$K2)*(OFFSET($A$2:$A$82,0,$K$1)=$K$1)*OFFSET($A$2:$A$82,0,$K$1),,),0)

Here is an alternate array formula for K3,

=SMALL(IF($A$2:$A$82=$K$2,IF(OFFSET($A$2:$A$82,0,$K$1)=$K$1,ROW($1:$81),1E+99),1E+99),ROW(1:1))

That requires Ctrl+Shift+Enter, but as a bonus you simply fill down to get the second, third, etc. match.

Answer 3

Hi Jeeped,

I have used the third formula (i.e. the array formula) and this works very well. Thanks very much. Can you please explain why the two false statements for the IF Functions have to use 1E+99. I have tried playing with this and if the value that is used for the IF false statements is lower than the result that the overall SMALL function would produce, then the formula stops working. Hence 100 is OK as the theoretical max answer is 81.

However, I would have throught as long as there was a record that matched with both K1 and K2, that the false statements would not impact the answer and hence 0 could have been used. I would be very grateful if you could explain the flaw in my reasoning.

Thanks very much.

Phil.