Hi Guy,
For testing purposes, you should be using much simpler numbers in which the match/mismatch can be checked with little difficulty and great speed visually, rather than long numbers requiring close examination to determine if the formula is working correctly.
Formulas in your example:
C2: IF(COUNTIF(F,2)=1,"good",IF(A2="","missing"))
English: If there is 1 instance of the value 2 in column F, return "good", If not, then If A2 is 'empty' return a null string, otherwise return "missing"
COUNTIF(F,2) counts the instances of the value 2 in column F, omitting the header row (if row 1 is defined as a header row) from the count. The first IF test should return FALSE on every line (there are no 2s in column F, so COUNTIF will return 0 for every line), and pass control to the second IF.
A2 is not empty on any line, so all copies of the formula should return the text "missing".
Syntax for COUNTIF: COUNTIF(test-array, condition)
Filled down column C, COUNTIF(F,2)
F2: MATCH(D2&E2,A&B,0)
Syntax: MATCH(search-for, search-where, matching-method)
search-for, a single value: D2&E2 —the concatenation of the contents of D2 and E2—OK
search-where, a one dimension range — A&B (see below)
matching-method 0 ('find value') —MATCH throws an error if it cannot find the exact value specified
Match returns 1.
The concatenation operator joins two individual values into a single text string; it does not work with a range of values in the way that would be necessary to make this formula produce your expected results, Because works with a pair on individual values, when presented column only references MATCH will interpret these as applying to 'the cell on this row of the specified column.' In F2, it joins the contents of A2 and B2 into a single value, and that single value is the entire range from which MATCH attempts to find a match for D2&E2. The joined contents of A2 and B2 is a match of the joined contents of D2 and E2, and are the ONLY item in the range specified in the formula, so the match is with item 1 in the one item list, and match returns 1.
I do not know how the formula in column C comes up with "good" in three rows of column C, unless you are manually editing the formula to make the 2 in the version in C2 match the row for each of the others.
Does this do what you need?
Formulas (all are entered in row 2 and filled down to the end of their respective columns):
G2: A&B
This concatenates the contents of this row of the first two columns into a single value and places the combined values into a single list, useable by MATCH.
F2: IFERROR(IF((D2&E2)="","",MATCH(D2&E2,G,0)),"")
MATCH(looks for the concatenated values in this row of columns D and E in column G, returns the position in the list of those it finds, or returns a 'cannot find' error if the value is not found in the list.
IF is used as a switch, which suppresses the MATCH calculation if this row of columns A and B is empty.
IFERROR is used as an error trap to catch the errors thrown by MATCH and insert a null string in the cell in column F.
C2: IF(COUNTIF(F,ROW())=1,"good",IF(A2="","","missing"))
Essentially the same as your formula, with ROW() calculating the fixed value 2 (in C2).
Regards,
Barry
